VBA: Conver Text & # 8594; Number multiplied by 1

Question

I have a table associated with an 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 every time. I wrote a macro to create a report, but it's hard for me to convert the numbers. I tried to multiply the entire table by one (the table will lose the link, but this is not a problem, since I just copy the information to another book and they close the table without saving), and it worked when I did it manually, but when I do it through a macro, this will not work. I see that it is multiplied by 1, but the numbers are still texts.

Since my table is huge, trying to convert every cell is not a parameter, it would take forever to execute each cell and use the CStr function.

My code is:

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 very unpredictable. Do not use this. Actually find the last line, and then create your range, and then use it for your operations.

Also just changing the format will not convert them to a number. You will need to reproduce the behavior of F2-Enter .

Try ( 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