Results 1 to 4 of 4

How to Repeat Calculation for Multiple Range using Excel VBA?

This is a discussion on How to Repeat Calculation for Multiple Range using Excel VBA? within the Excel Questions forums, part of the Question Forums category; Hi, I've been trying to repeat the calculations for a range using excel VBA Here is the formula If Range("H9") ...

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    2

    Default 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. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,967

    Default 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
    
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    7,657

    Default 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. #4
    New Member
    Join Date
    May 2012
    Posts
    2

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com