How to use a Range in a Loop

[Guest]

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 Ogilvy]

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

 

[Guest]

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

Tom,

Thanks for the help... this should get me started quite nicely.

regards,