MrExcel Publishing
Your One Stop for Excel Tips & Solutions

"FOR" loop to march through cells


Posted by Franklin on September 07, 2001 12:36 PM

Can anyone help me with the syntax for a simple for loop to march through a set of cells? For example, I want to go through a range of cells,activate and then speak them. Here is what I think the code might look like(with improper syntax...)

Private Sub CommandButton2_Click()
Dim i As Integer

For i = "b" To "z"

Range("[i]67").Speak
Range("[i]40").Activate
Range("[i]40").Speak
Next

End Sub

Please ignore the poor syntax as I am just picking up VBA. Thanks in advance for your help.


Posted by Barrie Davidson on September 07, 2001 12:48 PM

For i = "b" To "z" Range("[i]40").Activate Range("[i]40").Speak Next

Have you tried,

Private Sub CommandButton2_Click()
Dim i As Integer

For i = 2 To 26

Cells(67,i).Speak
Cells(40,i).Activate
Cells(40,i).Speak
Next

End Sub

I don't know if it will work (I didn't test it).

Regards,
Barrie

Posted by Franklin on September 07, 2001 2:04 PM

Brilliant! Works perfect... For i = "b" To "z" Range("[i]40").Activate Range("[i]40").Speak Next