How can I simplify this expression in VBA

Cole Williams 0
Viewed 46
    Range("K4") = FormatPercent(Range("K2") / Range("K3"))
    Range("L4") = FormatPercent(Range("L2") / Range("L3"))
    Range("M4") = FormatPercent(Range("M2") / Range("M3"))
    Range("N4") = FormatPercent(Range("N2") / Range("N3"))

I was able to get this to print what I wanted but I'm sure there is a simpler way

I tried Range("K4:N4") = FormatPercent(Range("K2:N2") / Range("K3:N3")) but it gave me an error. I'm still new to VBA and was looking for advice. Maybe something with a For loop would work better.

vba excel
1 Answers

Write Percentages

  • FormatPercent will convert the result to a string.
  • If you need the percentages as numbers, you could use one of the following.
Sub WritePercentages()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Using 'Worksheet.Evaluate' with hard-coded source ranges' addresses
    ' ('K2:N2, K3:N3') to write values.
    With ws.Range("K4:N4")
        .NumberFormat = "0.00%"
        .Value = ws.Evaluate("K2:N2/K3:N3")
    End With

    ' Using 'Worksheet.Evaluate' with 'Range.Offset' to retrieve
    ' the source ranges' addresses ('$K$2:$N$2, $K$3:$N$3') to write values.
'    With ws.Range("K4:N4")
'        .NumberFormat = "0.00%"
'        .Value = ws.Evaluate(.Offset(-2).Address & "/" & .Offset(-1).Address)
'    End With

    ' Using 'Range.Formula' with hard-coded source ranges' addresses
    ' ('K2, K3') to write formulas ('K2/K3', 'L2/L3'...).
'    With ws.Range("K4:N4")
'        .NumberFormat = "0.00%"
'        .Formula = "=K2/K3"
'    End With

End Sub
VBasic20080
Related