# Help with Macro Loop

##### Board Regular
I am using the following macros to look down column A looking for "T" and then wish to place some formulas in Columns F&G where "T" is found. The problem lies in the Total macro, I do not know what commands to use, as the ones I have do not work.

Hope someone can help.

Sub MonthlySummary()
Dim i As Long
For i = Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1
If Cells(i, "A").Value = "T" _
Then Call Total
Next i
End Sub

Sub Total()
Cells(i, "F").FormulaR1C1 = "=+RC[-4]+RC[-3]"
Cells(i, "G").FormulaR1C1 = "=+RC[-5]+RC[-4]"
End Sub

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Smitty

##### Legend
Why not just do it all in one shot:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MonthlySummary()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "C").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
<SPAN style="color:#00007F">If</SPAN> Cells(i, "A").Value = "T" <SPAN style="color:#00007F">Then</SPAN>
Cells(i, "F").FormulaR1C1 = "=RC[-4]+RC[-3]"
Cells(i, "G").FormulaR1C1 = "=RC[-5]+RC[-4]"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note I removed the leading "+" in the formulas as they're unnecessary.

HTH,

Smitty

#### mikerickson

##### MrExcel MVP
MonthlySummary declares i as a variable, but there is no declaration in the sub Total. Total doesn't "know" what i is.
There are two ways to solve this.

1) Declare i as a module wide variable. Instead of putting the Dim statement inside MonthlySummary, put it before the Sub MonthlySummary() line. That way all of the subs in that module share the value of i.

2) Pass it as an argument.
Code:
``````Sub MonthlySummary()
Dim i As Long
For i = Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1
If Cells(i, "A").Value = "T" _
Then Call Total(i)
Next i
End Sub

Sub Total(rowNumber As Long)
Cells(rowNumber, "F").FormulaR1C1 = "=+RC[-4]+RC[-3]"
Cells(rowNumber, "G").FormulaR1C1 = "=+RC[-5]+RC[-4]"
End Sub``````
The Call Total(i) passes the value of i to the sub Total (where it is called rowNumber).

The VB help system keywords "scope" and "argument" has more on this topic.

##### Board Regular
Thanks for the help.

Replies
0
Views
103
Replies
1
Views
341
Replies
2
Views
264
Replies
2
Views
217
Replies
3
Views
129

1,191,125
Messages
5,984,786
Members
439,911
Latest member
dk73

### 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