How to use .Numberformat in VBA with variable string and integer value?-CodePudding

Is there any way I can get a number format to look like this in the spreedsheet cell through VBA code?: EMBASAMENTO - ANDAMENTO GERAL: 50%. Nonetheless the "EMBASAMENTO" is a variable of the type string, "-ANDAMENTO GERAL:" is a permanent text, and "50%" is the value that is calculated and set to the cell in percentage format.

Private Sub Worksheet_Change()
   Nfil = 3
   Ntot = 5
   Model = "EMBASAMENTO"
   Piler = Nfil / Ntot
   Range("E7").Value = Piler
   Range("E7").NumberFormat = """ - ANDAMENTO GERAL: ""0%"
End Sub

CodePudding user response：

The code I posted in the question is working. What I need and I am trying to do is something like this below, but it's not working:

Private Sub Worksheet_Change()
  Nfil = 3
  Ntot = 5
  Model = "EMBASAMENTO"
  Piler = Nfil / Ntot
  Range("E7").Value = Piler
  Range("E7").NumberFormat = Model & """ - ANDAMENTO GERAL: ""0%"
End Sub

The final result of this code should be EMBASAMENTO- ANDAMENTO GERAL: 60%

CodePudding user response：

You will need quotes before the variable too, like this:

Private Sub Worksheet_Change()
  Nfil = 3
  Ntot = 5
  Model = "EMBASAMENTO"
  Piler = Nfil / Ntot
  Range("E7").Value = Piler
  Range("E7").NumberFormat = """" & Model & "- ANDAMENTO GERAL: ""0%"
End Sub

Not sure the Worksheet_Change event is the right place for this, but that's not part of your question.