Converting numbers stored as text to regular numbers query (VBA)

[Markylex]

Hello all,

I'm back here after a long time, once again seeking some support with another VBA macro! This time around, I have a query associated to converting numbers stored as text to regular numbers, where I am carrying this out across a couple different worksheets and I am working with a dynamic data range on each sheet.

At the moment, this is the VBA code I have:

Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("Voyage Data Rec")

Dim ws3 As Worksheet
Set ws3 = ThisWorkbook.Sheets("Sun Extract")

With ws2.Range("E2:E" & Cells(Rows.Count, "F").End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With

With ws3.Range("F2:F" & Cells(Rows.Count, "G").End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With

In terms of what I am hoping to achieve, for each of the two worksheets mentioned above, I am firstly trying to find out the end point of my data, based on the last row in column "F" and "G" respectively. Secondly, I then wish to apply the format changes to specific ranges, starting at row "E2" and "F2" respectively and finishing at those 'end points'. I am currently finding however, that the code is only partially working, as it appears to be stopping before what I'd expect each end point to be, so not all of the data is converted to a regular number. Where the worksheet titled "Voyage Data Rec" has roughly 120 rows in this particular data set I am working with, the macro appears to be going to around line 60 only. With the worksheet titled "Sun Extract", where this has roughly 5000 rows, the macro is only going to around line 50 only.

I am unsure if the above is incorrectly written/missing important code in order to function as intended, or if the problem lies with something else in the overall code 'clashing' in some way. In the process of testing the above however, I have noticed that, where my entire macro usually runs from a separate sheet to those named above and 'fails', if I run the macro line by line with the worksheet "Voyage Data Rec" open, I get the result I intended for that particular sheet. As the above code is located at the very top of the entire macro, I am thinking this does indeed point to the above code being the problem. Furthermore, all the code after this seems to provide the expected result.

I hope this makes sense and should you require any further information, such as the entirety of the macro, then let me know and I am happy to provide.

Thanks in advance for any support you can provide and apologies if the issue is a glaringly obvious one - I'm still not the best with VBA!

 

[t0ny84]

Do you have any blank cells in these columns?
The below should change the numbers from General to Numbers.

If you update your code:

From:

With ws2.Range("E2:E" & Cells(Rows.Count, "F").End(xlUp).Row)
.NumberFormat = "General"

To:

With ws2.Range("E2:E" & Cells(Rows.Count, "F").End(xlUp).Row)
    .NumberFormat = "0.00" ' Replace "0.00" with the desired number format
End With

t0ny84

 

[Alex Blakenburg]

See if this fixes your issue:
Currently your Cells(Rows.Count.... is getting the last row using the ActiveSheet. I have added the worksheet reference to that.

Sub ConvertTextNumbers()
Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("Voyage Data Rec")

Dim ws3 As Worksheet
Set ws3 = ThisWorkbook.Sheets("Sun Extract")

With ws2.Range("E2:E" & ws2.Cells(Rows.Count, "F").End(xlUp).Row)
    .NumberFormat = "General"
    .Value = .Value
End With

With ws3.Range("F2:F" & ws3.Cells(Rows.Count, "G").End(xlUp).Row)
    .NumberFormat = "General"
    .Value = .Value
End With

End Sub

 

[Markylex]

Thanks both for getting back to my query.

@Alex Blakenburg I can't believe I didn't notice this! I amended the code as per your suggestion and it now works perfectly - thanks a lot, it's most appreciated.

@t0ny84 The raw data I am working with for this macro is different each time. Where the above code is adjusting the formatting on an 'invoice number' column, sometimes this will be consist of purely numbers and other times will be a mixture of letters + numbers. That in mind, should the "NumberFormat" remain as General?

 

[Alex Blakenburg]

Thanks for responding to both of us. Glad we could help.
Not sure if I should leave it to tony to answer but I since I am here now, the NumberFormat will only impact anything Excel is recognising as a number. So you can apply a number format to a mixed (Text-Number) column. One thing some of the formats will do to text is indent the text by 1 character.

Once Excel recognises it as Text changing the display format to Number will "not" convert what is already there. To convert numbers you would need to firstly make sure the display format is not "text" and then effectively reenter the number. This can be done manually (edit > enter) but generally done by PasteSpecial (Muliply by 1 or Add 0) or Data > Text to Columns or in VBA .Value = .Value
A quick test to see if Excel is recognising something as a number (includes dates) is to change the number format. If there is no change Excel is seeing it as text.

You shouldn't have a field/column that you are going to use for calculations having anything other than numbers in it though. If it is a reference field and has mixed data types, from a database perspective it should all be treated as Text but this is hard to enforce in Excel and often causes lookups to fail.