How to use a Range in a Loop
"Tom Ogilvy" wrote:
Dim cell as Range
for each cell in Range("B3:B25")
if not isempty(cell) then
if isnumeric(cell) then
cell.Formula = "=SUM(C" & cell.row & ":H" & cell.row & ")"
end if
end if
Next
You can certainly use a variable
Dim sStart as String, sStop as String
Dim cell as Range
sStart = "C"
sStop = "H"
for each cell in Range("B3:B25")
if not isempty(cell) then
if isnumeric(cell) then
cell.Formula = "=SUM(" & sStart & cell.row & ":" & sStop & cell.row &
")"
end if
end if
Next
--
Regards,
Tom Ogilvy
"Glen" wrote in message
...
I was wondering how you can use a range for a loop start and end positions.
Give a range B3:B25
I wouldlike to loop through each cell and if there exists a value (any
number including zero) then replace the value with the associated formula
...
in this case if B3, B17 and B23 were numbers then
B3 becomes =sum(C3:H3)
B17 becomes =sum(C17:H17)
B23 becomes =sum(C23:H23)
Also can variables be used as cell references
dfrank = C17
B17 becomes =sum(dfrank:H17)
thanks
Tom,
Thanks for the help... this should get me started quite nicely.
regards,
|