Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use a Range in a Loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use a Range in a Loop
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Loop | Excel Programming | |||
Add a Range to an existing Range in a loop? | Excel Programming | |||
Loop through a range | Excel Programming | |||
loop in range | Excel Programming | |||
Loop in a Range | Excel Programming |