By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

For next Loop With String Values

P: n/a
I have this wonderful piece of code for calculating a running total in
one column as I am exporting a Access table to Excel. I am trying to
use the same piece of code for multiple columns so that I don't have to
write/maintain the same piece of code 5 times.

Can someone please help me with the syntax for the first line (For
strColumnLetter = "E" To "I") so that the code will loop through those
letters (E, F, G, H, I)?

Thank you

For strColumnLetter = "E" To "I"
dblTotal = 0
For nptr = 9 To (9 + rsFL026.RecordCount)
If appExcel.Range(strColumnLetter & nptr).Value <"" Then
dblTotal = dblTotal + appExcel.Range(strColumnLetter &
nptr).Value
End If
Next

.Range(strColumnLetter & (9 + rsFL026.RecordCount)).FormulaR1C1
= dblTotal
.Range(strColumnLetter & (9 + rsFL026.RecordCount)).Font.Bold =
True

Next

Jan 17 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I would instead loop through the numbers 5 to 9 and reference them
using Cells instead of range.

For nCol = 5 To 9
dblTotal = 0
For nPtr = 9 To (9 + rsFL026.RecordCount)
If appExcel.Cells(nPtr, nCol).Value <"" Then
dblTotal = dblTotal + appExcel.Cells(nPtr, nCol).Value
End If
Next
.Cells((9 + rsFL026.RecordCount), nCol).FormulaR1C1 = dblTotal
.Cells((9 + rsFL026.RecordCount), nCol).Font.Bold = True
Next

Cheers,
Jason Lepack

tj*****@adelphia.net wrote:
I have this wonderful piece of code for calculating a running total in
one column as I am exporting a Access table to Excel. I am trying to
use the same piece of code for multiple columns so that I don't have to
write/maintain the same piece of code 5 times.

Can someone please help me with the syntax for the first line (For
strColumnLetter = "E" To "I") so that the code will loop through those
letters (E, F, G, H, I)?

Thank you

For strColumnLetter = "E" To "I"
dblTotal = 0
For nptr = 9 To (9 + rsFL026.RecordCount)
If appExcel.Range(strColumnLetter & nptr).Value <"" Then
dblTotal = dblTotal + appExcel.Range(strColumnLetter &
nptr).Value
End If
Next

.Range(strColumnLetter & (9 + rsFL026.RecordCount)).FormulaR1C1
= dblTotal
.Range(strColumnLetter & (9 + rsFL026.RecordCount)).Font.Bold =
True

Next
Jan 17 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.