VBA to convert to number

[steelervince]

I have a string of imported data which comes into an existing spreadsheet. It cannot be changed prior to importing as the data is provided by an external vendor.

When the data is imported into my spreadsheet the Error Checking tag comes on, after I select the cells where certain numeric data has been entered, stating that this is a range of Numbers Stored as Text. If I click on the Error Checking tag and select the Convert to Number option and then enter my VLOOKUP function I get the desired answer. If I do not convert the text to numbers then my VLOOKUP function does not work at all. The cell just displays the function text itself instead of the desired answer.

Here is what I am requesting:

Assistance with writing the VBA code to accomplish the manual task of converting the text to numbers. I have tried to format using the Ribbon without success.

Any ideas out there?

 

[MARK858]

@purinqui as with the Evaluate code earlier you will get an error if there is any actual text in the range (it is also slower than the Evaluate code for the same method).

 

[purinqui]

@purinqui as with the Evaluate code earlier you will get an error if there is any actual text in the range (it is also slower than the Evaluate code for the same method).

It is right, but is easy to fix. Simply control every cell with the function ISNUMERIC before the loop.

The code that i've posted is the way to convert to number all cells that has been stored as text.


When you have cells in this range that could contain another values or none values. You should use the accurate controls in order to work properly.

 

[MARK858]

Personally I prefer Text to Columns and I would prefer Evaluate to looping through the cells.

 

[purinqui]

Personally I prefer Text to Columns

How could implement this solution in VBA?
Is easy to do manually through the excel environment. But through VBA? Thanks.

 

[MARK858]

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

or if you wanted to shorten it (no time difference gained)

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
         Other:=False, FieldInfo:=Array(1, 1)