How to Repeat Calculation for Multiple Range using Excel VBA?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    17,767
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,758
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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