How to Repeat Calculation for Multiple Range using Excel VBA?

[steve1011]

Hi, I've been trying to repeat the calculations for a range using excel VBA

Here is the formula

If Range("H9") = "Repair" Then
Range("K9") = Range ("K9") + Range("G9")

If Range("H9") = "Service" Then
Range("L9") = Range ("L9") + Range("G9")

End If

Can someone please shed some lights on how to repeat this for a range, says 9 to 20?

Thanks a lots

Regards,
Steve

 

[Domenic]

Try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]For[/color] i = 9 [color=darkblue]To[/color] 20
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] Cells(i, "H").Value
            [color=darkblue]Case[/color] "Repair"
                Cells(i, "K").Value = Cells(i, "K").Value + Cells(i, "G").Value
            [color=darkblue]Case[/color] "Service"
                Cells(i, "L").Value = Cells(i, "L").Value + Cells(i, "G").Value
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

However, if you don't want this to be case-sensitive, try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]For[/color] i = 9 [color=darkblue]To[/color] 20
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] U[color=darkblue]Case[/color](Cells(i, "H").Value)
            [color=darkblue]Case[/color] "REPAIR"
                Cells(i, "K").Value = Cells(i, "K").Value + Cells(i, "G").Value
            Case "SERVICE"
                Cells(i, "L").Value = Cells(i, "L").Value + Cells(i, "G").Value
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[JoeMo]

try:

Sub test()
For rws = 9 To 20
    If Range("H" & rws) = "Repair" Then
        Range("K" & rws) = Range("K" & rws) + Range("G" & rws)
    ElseIf Range("H" & rws) = "Service" Then
        Range("L" & rws) = Range("L" & rws) + Range("G" & rws)
    End If
Next rws
End Sub

 

[steve1011]

Hi Domenic and Joemo, thanks for the help, it works great.

Thanks.

Regards,
Steve