| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range Addressing in For Loop
Hello, I am trying to write a macro that will add the values in a range of
cells with the values in the adjecent cells then clear those adjecent cells. I have "hard coded" the macro by typing in each and every cell address, this works, but I would like to use a For Loop: --------------------------------- Sub OptionButton1_Click() Dim z As Integer For z = 8 To 22 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 26 To 40 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 46 To 51 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z End Sub --------------------------- This does not work, Is there some way to signify that the second character in the cell address ("iz" the z) is the for loop counter ie z??? Stephen William Foyle |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range Addressing in For Loop
Sub OptionButton1_Click()
Dim z As Integer For z = 8 To 22 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z For z = 26 To 40 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z For z = 46 To 51 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z End Sub -- Regards, Tom Ogilvy "Stephen William Foyle" wrote: Hello, I am trying to write a macro that will add the values in a range of cells with the values in the adjecent cells then clear those adjecent cells. I have "hard coded" the macro by typing in each and every cell address, this works, but I would like to use a For Loop: --------------------------------- Sub OptionButton1_Click() Dim z As Integer For z = 8 To 22 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 26 To 40 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 46 To 51 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z End Sub --------------------------- This does not work, Is there some way to signify that the second character in the cell address ("iz" the z) is the for loop counter ie z??? Stephen William Foyle |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range Addressing in For Loop
Thanks Tom It Works!
-- Stephen William Foyle "Tom Ogilvy" wrote: Sub OptionButton1_Click() Dim z As Integer For z = 8 To 22 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z For z = 26 To 40 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z For z = 46 To 51 Range("i" & z).Value2 = Range("i" & z).Value2 + Range("j" & z).Value2 Range("j" & z).Value2 = "" Next z End Sub -- Regards, Tom Ogilvy "Stephen William Foyle" wrote: Hello, I am trying to write a macro that will add the values in a range of cells with the values in the adjecent cells then clear those adjecent cells. I have "hard coded" the macro by typing in each and every cell address, this works, but I would like to use a For Loop: --------------------------------- Sub OptionButton1_Click() Dim z As Integer For z = 8 To 22 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 26 To 40 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z For z = 46 To 51 Range("iz").Value2 = Range("iz").Value2 + Range("jz").Value2 Range("jz").Value2 = "" Next z End Sub --------------------------- This does not work, Is there some way to signify that the second character in the cell address ("iz" the z) is the for loop counter ie z??? Stephen William Foyle |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA and range addressing | Excel Discussion (Misc queries) | |||
| loop through cells in a range and pick up corresponding cell values in another range | Excel Programming | |||
| how do i maintain named range addressing after deleting row? | Excel Programming | |||
| Add a Range to an existing Range in a loop? | Excel Programming | |||
| indirect addressing? a range of cells | Excel Programming | |||
Linear Mode
