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

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

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