Modification needed on VBA code to Reverse Abbreviate Numbers.

[darkwingduck1]

Original code thread: How Can I Reverse Abbreviated Numbers?

As a beginner with Excel Macros and VBA I've got this half working as I need it to, but I've ran into a couple problems (I'm on Excel 2019):

- When it comes across cells with Billion (eg 2.99B) I get runtime error 6 "overflow". I gather it's because the number is too large, I've tried changing the cell format to number but that doesn't work.
- Is there a way to modify the code so I can click it and it will do all the required columns in one go? The Columns I need it to work in are: P, R, T, X, AR, AS, DE, DF (all from row 4)

Note:
In the loop section I've changed rngCell.Offset(, 1) to rngCell.Offset(, 0) so it changes the number in the current cell as opposed to the cell next to it

Would really appreciate any help.

Thanks
Ron

Function f_Numerize(strValue As String) As Long
'Created 17 Aug 2020 by Wookiee at MrExcel.com

'Declare Variables
Dim dblNumber As Double
Dim dblNA As Double
Dim dblK As Double
Dim dblM As Double
Dim dblB As Double
Dim strEvaluation As String

'Search For Text Values To Convert
dblNA = VBA.InStr(strValue, "N/A")
dblK = VBA.InStr(strValue, "K")
dblM = VBA.InStr(strValue, "M")
dblB = VBA.InStr(strValue, "B")

strEvaluation = VBA.UCase(strValue)

If dblNA > 0 Then

   f_Numerize = 0
   Exit Function
 
ElseIf dblK > 0 Then

   dblNumber = VBA.CDbl(Replace(strValue, "K", ""))
   f_Numerize = dblNumber * 1000
   Exit Function
 
ElseIf dblM > 0 Then

   dblNumber = VBA.CDbl(Replace(strValue, "M", ""))
   f_Numerize = dblNumber * 1000000
   Exit Function
 
ElseIf dblB > 0 Then

   dblNumber = VBA.CDbl(Replace(strValue, "B", ""))
   f_Numerize = dblNumber * 1000000000
   Exit Function
 
End If

End Function

To test, I employed that function in a loop like this:

Sub LoopIt()

Dim rngCell As Range

For Each rngCell In Selection

   rngCell.Offset(, 1) = f_Numerize(rngCell.Value)
 
Next rngCell

End Sub

 

[darkwingduck1]

UPDATE:
I've resolved the billions issue by changing "Dim dblB As Double" to "Dim dblB As Long"

Would appreciate help to modify the code so it will update all the required columns on one macro run instead of having to manually highlight all the cells I need to change (the spreadsheet is quite big, goes to DK9050)

 

[darkwingduck1]

UPDATE:
Sorry for being such a noob, and I can't see a way to delete or edit posts but some cells still give me the overflow error with B (billions). Any help would be much appreciated.

 

[Peter_SSs]

I can't see a way to delete or edit posts

You cannot delete posts. Now that you are an established user, when you make a post you should see an Edit button at the bottom left of your post - but it is only available for 10 minutes after you first make that post.

 

[Peter_SSs]

Now that you are an established user,

Actually, since I deleted your duplicate question posts in an old thread, you will need a couple more posts before you will have the ability to edit.