# Looping Question "Very Specific" Excel VBA 2007

#### aholts

##### Board Regular
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, "##%")

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
aholts,

If all that's changing is Mth1 and Mth2 names, just use this for everything:
Code:
``````    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, "##%")``````

Replies
2
Views
106
Replies
4
Views
111
Replies
0
Views
295
Replies
9
Views
671
Replies
5
Views
168

1,202,990
Messages
6,052,958
Members
444,621
Latest member
MIKOLAJ_R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back