Help with Macro Loop

[bradmw]

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

 

[Smitty]

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]

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.

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.

 

[bradmw]

Thanks for the help.