CODEWITHSUNDEEP
vbaexcel
B.Halbur
B.Halbur

Reputation: 11

Using a string variable to set value of .NumberFormat in Excel VBA

I'm trying to set a custom number format with the following result:
the value "22,800" becomes "Europe: 22.8 M"

I can achieve this using the number formatting window, and using:
"Europe:" #,##0.0, "M"; "Europe:" -#,##0.0, "M"

I can also achieve this in VBA window using
ActiveCell.NumberFormat = """Europe:"" #,##0.0, ""M""; ""Europe:"" -#,##0.0, ""M"""

Here is the issue: I'm running a loop function which takes the values from different sheets and pastes them onto a summary sheet - I need the region (e.g. "Europe") to show up next to the number, but I don't want to write the code for the loop function 9 different times for my 9 regions.

My attempted solution, here I would just change the value of the variable "region" for each loop:

Dim region As String
Dim numformat As String
region = "Europe"
numformat = Chr(34) + Chr(34) + Chr(34) + region + ":" + Chr(34) + Chr(34) + " #,##0.0, " + Chr(34) + Chr(34) + "M" + Chr(34) + Chr(34) + "; " + Chr(34) + Chr(34) + region + ":" + Chr(34) + Chr(34) + " -#,##0.0, " + Chr(34) + Chr(34) + "M" + Chr(34) + Chr(34) + Chr(34)
Selection.NumberFormat = numformat

Which formats the number as "Europe: #,##0.0, M; Europe: -#,##0.0, M" - it doesnt show the number at all, just this string.

However, if I use ?numformat in the immediate window to return the string's value, and then copy/paste that value so that
Selection.NumberFormat = """Europe:"" #,##0.0, ""M""; ""Europe:"" -#,##0.0, ""M"""

Then, I get the correct number format, "Europe: 22.8 M"

But this doesn't help me, because now the region name is hard-coded again. Can anyone help identify what I'm doing wrong, or identify an alternative solution?

Cheers,

B

Upvotes: 0

Views: 2006

Answers (1)

Rory
Rory

Reputation: 34045

Use:

    numFormat = """" & region & ": ""#,##0.0,""M"";""" & region & ":"" -#,##0.0, ""M"""

or, if you really want to use Chr(34) you don't need to double them, because you don't need to escape them:

numFormat = Chr(34) & region & ": " & Chr(34) & "#,##0.0," & Chr(34) & "M" & Chr(34) & ";" & Chr(34) & region & ":" & Chr(34) & " -#,##0.0, " & Chr(34) & "M" & Chr(34)

Upvotes: 2

Related Questions