Number Stored as Text -> Convert to Number

[ppayjj]

Hello, I am attempting to record a macro for a project of mine. One issue I have is that the data I am working with gets pulled from SAP and the numbers I need to use a match function initially have the error "Number Stored as Text." In order for the match function to work, I highlight the entire columns with this error and just click Convert to Number. I have attached a picture of this error. When I do this while recording a macro, it does not work on a new file.

I have 3 columns that I need to do this transformation on. This is what I currently have below. It works if I break it up and perform on one sheet at a time. But when I attempt to do it all at once, I get the error "Select Method of Range Class Failed."

Please Login or Register  to view this content.

I realize there may be a simple fix for this, I am pretty inexperienced with VBA and appreciate all the help. Thanks.

[DavidBowman]

You are converting columns H, T, and R. The code you have is careful to make the conversion on a specific named worksheet on the second two macros converting columns T and R. The macro to convert column H is not specific and more general. You mentioned you are trying to change it on multiple sheets all at once. Can I clarify? Are you wanting to change the same columns on sheets with multiple names? Are you wanting to just highlight the columns you want to convert on the current worksheet and convert them? Are you trying wanting to highlight the same column but on different sheets to convert them?

[bakerman2]

@ ppayjj

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[ppayjj]

There are 3 columns that need to be changed, on two different sheets. Column H on the first sheet, and columns T and R on the other sheet. The one for column H is on the first datasheet and the one that is opened automatically so I did not specify that sheet. I know that the "select" command on VBA is quite infamous and is most likely the source of my problems, but I am unsure of any other way to do it.

Thanks again.

[shg]

Please Login or Register  to view this content.

[DavidBowman]

Often the problem can be solved by activating the worksheet you want to work on. Give this a try:

Please Login or Register  to view this content.