Convert Data to Number (VLookup Error)

[ExcelMacLean]

Hello,

I have a simple program with two columns of data.
Col 1: The database column of over 3000 values
- usually 13 numbers in a row
- sometimes contains a letter in a random location
Col 2: a smaller column filled with some test-values

There is a V-Lookup function which simply scans the values in column 1 to see if they are in the database column.

The Vlookup does not work unless the values are all stored as the same data type in both columns. I can use the built in excel error-help feature "Convert to Number" on both columns, and this makes the vlookup work properly.

(I would post a picture but my works internet has limited functionality. I see the "Convert to Number" help-option when I click on the "!" icon next to the cell. the error message is, "the number in this cell is formatted as text or preceded by an apostrophe.")

I need to know the VBA code to accomplish this. When I attempted to record the macro of the error-correction excel did the conversion, but did not record anything.

Reason: The user has to copy/paste in the data into the tool for it to work. I want to make sure the tool converts all the values to numbers so the Vlookup works regardless of what data the user puts into it.

What I have tried:

Dim c As Range

     'Select the entire data column
    For Each c In Selection.Cells
        If IsNumeric(c.Value) = False Then
            c.Value = Val(c.Value)
            End If
        Next

Thanks in advance.

 

[svendiamond]

Your comment says "select the entire data column" but it doesn't actually select anything... anyway, I would try this - assuming your data is in column 1, Sheet1. If the cell is formatted as text it might still look like text, so I added the "NumberFormat" line to ensure it changes to a number.

Sub converter()


With Sheets("Sheet1")
    Dim lastRow As Long, myLoop As Long
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For myLoop = 1 To lastRow
            .Cells(myLoop, 1).Value = Val(.Cells(myLoop, 1).Value)
            .Cells(myLoop, 1).NumberFormat = "#,###"
        Next myLoop
End With


End Sub