What is wrong with this code?

[Lou_Reed]

In the Excel sheet that I have attached when I press the button to Change Select Text to Numbers, I get


the error shown in capture the graphic.

The dialogue box that opens when I try to assign the macro changetextonumber()

Shows that macro very differently. It is a very long line of letters and words

How to fix this?

R,

Lou Reed

[Lou_Reed]

Ignore this. I have the answer. I think.

R,

Lou Reed

[June7]

I use this code:

Dim c As Range
For Each c In Selection
    c = c.Value
Next c

And had to reassign the macro to the button.

[John_G]

Here you are. For some reason the macro was incorrectly assigned to the button, so I reassigned it and the error went away.

I also amended the two change macros a bit. The (misspelled) macro chnagetexttonumber didn't work at all, so I fixed the spelling and rewrote it (almost the same as changenumbertotext), and I amended both of them to make changes to cells only if a) the cell was not blank AND b) the value in it was numeric, using the len() and isnumeric() functions. That way it doesn't bother with cells which are already non-numeric text.

The very useful isnumeric() function works whether the value you give it is a number or a character string that looks like a number, so isnumeric(23) and isnumeric("23") both evaluate to True.

[Lou_Reed]

First how can you tell that a macro is not correctly assigned? I just write the macro compile it, rewrite code if errors are present, and then recompile.

Then I put a form control or button on the Excel sheet, when finished, it asks me to assign a macro. I do.

If is wrong the only way I know is to check code(repair if needed) and recompile.

I cannot just get a list of macros an the buttons they are assigned to.

Also, why did you leave out select cells in your code/example?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

I uploaded the db that you posted. I also uploaded the Excel. Then I went through the required steps to prepare the Excel file to be imported to Access and impoted it.

I got an error file as you can see.

I thought that I select all of the cells apparently I did not.

How do I get rid of the error file?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

Did you notice that all the errors were in the same column - Gate 22? I figured that there must be something different about that column, and there is. There is no data (text or numeric) in any of the cells until Row 51, where there is a "10" - as text (i.e. it has the Apostrophe flag in it). My guess is that when Access is doing the import and deciding what the field types are, it doesn't look as far as Row 51, so it makes the table field a numeric by default.

A quick fix is to put the word "Text" in Row 2 of that column, and it imports fine, no errors. You can always edit the "Text" out of that row after the import. But there was nothing wrong with the Excel file; it was just the quirky way Access works sometimes.

[Lou_Reed]

Okay, I understand. I did get it to import before with no errors at all.

I wonder what is causing this to occur only occasionally.

Respectfully,

Lou Reed

[Lou_Reed]

I am curious about my command on my Excel spreadsheet to select cells. It works most of the time, then again it fails at times. I have to press the control at least twice to get all of the cells select and I would think once would be enough.

Also, I noticed in your solution you did not even have such a control, I guess that you must have used cntrl-A. When I do that I must press cntrl-A at least twice. So I am not sure that my "Select Cells" form control
is any better than just pressing I would be interested in your thoughts on this.

I have attached my Excel spreadsheet for your examination.

Any help appreciated. Thank in advance.

Respectfully,

Lou Reed

Respectfully,

Lou Reed

[June7]

The latest posted file has:

Dim Rng As Range
Set Rng = Range(Cells(1, 1), ActiveCell)
'Selecting only hardcoded data
Rng.SpecialCells(xlCellTypeConstants).Select


Another version of your workbook has this code to select cells:

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants).Select

This version works on one click.