Looping Question "Very Specific" Excel VBA 2007

[aholts]

Hello,

I am having a challenge with wrapping my head around a problem that I know needs to be resolved with variables and looping but I am not sure how to do it.

I have 12 sheets in a workbook that are named after the months of the year. I have filled to combo boxes with the names of the months because I want to perform a calculation which takes the value of one month and compares it with another month. Basically a "cross sheet calculation" as I call it.

The problem is that I can't get it to work. The problem is that I want to perform this calculation for like 5 categories and they way I am doing it is making my module code very cumbersome and probably even harder to maintain. As you will see below, I have had to repeat this code 144 times per category to catch all the combinations of comparing the different months. The only nice thing about this is that it is in a module and not my main code so I can call it whenever I need it. Below is a snippet of what I am trying to do. Any suggestions?

Sub JulSelection()
'This will calculate the Increase or Decrease in Gross Income
If UserForm1.Mth1ComparisonComboBox.Value = "July" And
UserForm1.Mth2ComparisonComboBox.Value = "January" Then
Sheets("Compare_Mthly_Progress").Select
Range("B4").Select
Range("B4").FormulaR1C1 = "=(((January!R[2]C+January!R[2]C[3])-(July!R[2]C+July!R[2]C[3]))/(July!R[2]C+July!R[2]C[3]))"
UserForm1.MthlyGrossComparisonTextbox.Value = Format(Range("B4").Value, "##%")

'This will calculate the increase or decrease in Total Debt
Range("B5").Select
Range("B5").FormulaR1C1 = "=(((January!R[24]C+January!R[24]C[3])-(July!R[24]C+July!R[24]C[3]))/(July!R[24]C+July!R[24]C[3]))"
UserForm1.TotalDebtComparisonTextBox.Value = Format(Range("B5").Value, "##%")

 

[tigeravatar]

aholts,

If all that's changing is Mth1 and Mth2 names, just use this for everything:

    Dim Mth1 As String: Mth1 = UserForm1.Mth1ComparisonComboBox.Text
    Dim Mth2 As String: Mth2 = UserForm1.Mth2ComparisonComboBox.Text
    
    'This will calculate the Increase or Decrease in Gross Income
    Sheets("Compare_Mthly_Progress").Range("B4").FormulaR1C1 = "=((('" & Mth2 & "'!R[2]C+'" & Mth2 & "'!R[2]C[3])-('" & Mth1 & "'!R[2]C+'" & Mth1 & "'!R[2]C[3]))/('" & Mth1 & "'!R[2]C+'" & Mth1 & "'!R[2]C[3]))"
    UserForm1.MthlyGrossComparisonTextbox.Value = Format(Range("B4").Value, "##%")
    
    'This will calculate the increase or decrease in Total Debt
    Sheets("Compare_Mthly_Progress").Range("B5").FormulaR1C1 = "=((('" & Mth2 & "'!R[24]C+'" & Mth2 & "'!R[24]C[3])-('" & Mth1 & "'!R[24]C+'" & Mth1 & "'!R[24]C[3]))/('" & Mth1 & "'!R[24]C+'" & Mth1 & "'!R[24]C[3]))"
    UserForm1.TotalDebtComparisonTextBox.Value = Format(Range("B5").Value, "##%")