How to Repeat Calculation for Multiple Range using Excel VBA?

Thanks:  0
Likes:  0

# Thread: How to Repeat Calculation for Multiple Range using Excel VBA?

1. ## How to Repeat Calculation for Multiple Range using Excel VBA?

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

2. ## Re: How to Repeat Calculation for Multiple Range using Excel VBA?

Try...

Code:
```Option Explicit

Sub test()

Dim i As Long

For i = 9 To 20
Select Case Cells(i, "H").Value
Case "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
End Select
Next i

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

Code:
```Option Explicit

Sub test()

Dim i As Long

For i = 9 To 20
Select Case UCase(Cells(i, "H").Value)
Case "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
End Select
Next i

End Sub
```

3. ## Re: How to Repeat Calculation for Multiple Range using Excel VBA?

try:
Code:
```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```

4. ## Re: How to Repeat Calculation for Multiple Range using Excel VBA?

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

Thanks.

Regards,
Steve

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•