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 |
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 |
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, |
All times are GMT +1. The time now is 04:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com