Looping

[Mavericks334]

Hi,

I have written a macro, that needs to run 6 times for a given cell and then proceed to the next cell and repeat this activity. However it does not work. Cell Range is increased in the 1st instance and the loop runs for a particular cell once before proceeding to the next cell.

ub SumFormula_InD()
Dim Cell As Range
Do
If ActiveCell.Offset(0, -2) = "Accts. Payable - Shareholder Div." Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""210.4200.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""210.4200.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""210.4200.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""210.4200.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""210.4200.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""210.4200.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""210.4200.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""210.4200.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""210.4200.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""210.4200.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""210.4200.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""210.4200.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""210.4200.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""210.4200.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""210.4200.C5026.USD"",""""))"
Else
If ActiveCell.Offset(0, -2) = "Distribution - S-T Cap. Gain" Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""420.4220.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""420.4220.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""420.4220.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""420.4220.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""420.4220.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""420.4220.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""420.4220.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""420.4220.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""420.4220.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""420.4220.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""420.4220.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""420.4220.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""420.4220.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""420.4220.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""420.4220.C5026.USD"",""""))"
Else
If ActiveCell.Offset(0, -2) = "Distribution - L-T Cap. Gain" Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""420.4210.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""420.4210.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""420.4210.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""420.4210.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""420.4210.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""420.4210.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""420.4210.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""420.4210.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""420.4210.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""420.4210.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""420.4210.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""420.4210.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""420.4210.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""420.4210.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""420.4210.C5026.USD"",""""))"
Else
If ActiveCell.Offset(0, -2) = "Distribution - SEC 1250 L-T Cap. Gain" Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""420.4210.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""420.4210.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""420.4210.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""420.4210.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""420.4210.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""420.4210.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""420.4210.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""420.4210.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""420.4210.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""420.4210.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""420.4210.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""420.4210.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""420.4210.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""420.4210.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""420.4210.C5026.USD"",""""))"
Else
If ActiveCell.Offset(0, -2) = "Distribution - Ordinary Income - Non QII" Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""420.4240.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""420.4240.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""420.4240.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""420.4240.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""420.4240.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""420.4240.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""420.4240.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""420.4240.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""420.4240.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""420.4240.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""420.4240.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""420.4240.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""420.4240.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""420.4240.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""420.4240.C5026.USD"",""""))"
Else
If ActiveCell.Offset(0, -2) = "Distribution - Ordinary Income - QII" Then
ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""420.4245.A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""420.4245.C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""420.4245.M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""420.4245.C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""420.4245.C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""420.4245.C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""420.4245.C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""420.4245.C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""420.4245.C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""420.4245.C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""420.4245.C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""420.4245.C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""420.4245.C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""420.4245.C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""420.4245.C5026.USD"",""""))"
End If
End If
End If
End If
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub

This is the macro please help.

 

[Jerry Sullivan]

Hi,

You might try using this as an outline and tailor it to your project. It uses a Select / Case statement which is an alternative to a series of ElseIf statements.

Option Explicit
 
Sub SumFormula_InD()
 
Dim c As Range 'This will be Cell currently being processed
Dim strLabel As String
Set c = Sheets("Sheet1").Range("C2")
Do
    strLabel = c.Offset(0, -2)
    Select Case strLabel
        Case "Accts. Payable - Shareholder Div."
            c.FormulaR1C1 = "My formula 1"
        Case "Distribution - S-T Cap. Gain"
            c.FormulaR1C1 = "My formula 2"
        Case "Distribution - L-T Cap. Gain"
            c.FormulaR1C1 = "My formula 3"
        Case Else
            'what to do if none of cases match...
             MsgBox strLabel & " doesn't match list of cases"
    End Select
    Set c = c.Offset(1, 0)
Loop Until IsEmpty(c.Offset(0, -2))
 
End Sub

Good luck!

 

[Rick Rothstein]

If I have read the code you posted correctly, I think you can replace all of it with this macro...

Sub SumFormula_InD()
  Dim Swap As String
 
  Select Case ActiveCell.Offset(0, -2)
    Case "Accts. Payable - Shareholder Div.": Swap = "210.4200"
    Case "Distribution - S-T Cap. Gain": Swap = "420.4220"
    Case "Distribution - L-T Cap. Gain": Swap = "420.4210"
    Case "Distribution - SEC 1250 L-T Cap. Gain": Swap = "420.4210"
    Case "Distribution - Ordinary Income - Non QII": Swap = "420.4240"
    Case "Distribution - Ordinary Income - QII": Swap = "420.4245"
  End Select
  ActiveCell.FormulaR1C1 = "=IF(Sheet2!R[5]C[16]=""A2019"",""" & Swap & ".A2019.USD"",IF(Sheet2!R[5]C[16]=""C5105"",""" & _
                           Swap & ".C5105.USD"",IF(Sheet2!R[5]C[16]=""Single"",""" & Swap & ".M2000.USD"",IF(Sheet2!R[5]C[-2]=""Class A"",""" & _
                           Swap & ".C5001.USD"",IF(Sheet2!R[5]C[-2]=""Class 1"",""" & Swap & ".C5501.USD"","""")))))&IF(Sheet2!R[5]C[-2]=""Class B"",""" & _
                           Swap & ".C5002.USD"",IF(Sheet2!R[5]C[-2]=""Class 2"",""" & Swap & ".C5502.USD"",""""))&IF(Sheet2!R[5]C[-2]=""Class B1"",""" & _
                           Swap & ".C5024.USD"",IF(Sheet2!R[5]C[-2]=""Class 3"",""" & Swap & ".C5503.USD"",""""))&IF(Sheet2!R[8]C[16]=""C5105"",""" & _
                           Swap & ".C5106.USD"",IF(Sheet2!R[5]C[-2]=""Class C"",""" & Swap & ".C5003.USD"",IF(Sheet2!R[5]C[-2]=""Class 4"",""" & _
                           Swap & ".C5504.USD"","""")))&IF(Sheet2!R[5]C[-2]=""Class R"",""" & Swap & ".C5018.USD"","""")&IF(Sheet2!R[5]C[-2]=""Advisor Class"",""" & _
                           Swap & ".C5026.USD"",IF(Sheet2!R[5]C[-2]=""Class Z"",""" & Swap & ".C5026.USD"",""""))"
End Sub