excel - VBA: Conver Text -> Number multiplying by 1

Question

I have a table linked to a SQL database that I use to create some reports. The problem is that the numbers come as text and I have to convert them to numbers everytime, I've wrote a macro to create the report but I'm having trouble to convert the numbers. I tried to multiply the whole table by one (the table lose the link but it's not an issue, since I'll just copy the infos to another workbook and them close the table w/o saving) and it worked when I did it manualy, but when I do it via macro it won't work. I can see it multiplying by 1 but the numbers are still texts.

Since my table is huge, trying to convert each cell is not an options sice it'd take forever to run each cell and use the CStr function.

My code:

Range("B2").Copy
Range("A4", Range("AC4").End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False

Any suggestions?

Answer 1

xlDown can be highly unpredictive. Don't use that. In fact find the last row and then create your range and then use that for your operations.

Also just changing the format will not convert those to number. You will have to replicate the F2-Enter behavior.

Try this (TRIED AND TESTED)

Sub Sample()
    Dim ws As Worksheet
    Dim Rng As Range, acell As Range
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("AC" & .Rows.Count).End(xlUp).Row

        Set Rng = .Range("A4:AC" & lRow)

        .Range("B2").Copy

        Rng.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

        Rng.NumberFormat = "0" ' OR "0.00" as applicable

        For Each acell In Rng
            acell.Formula = acell.Value
        Next
    End With
End Sub

SCREENSHOT