Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

"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.

Check out our Excel VBA Resources

Re: "FOR" loop to march through cells

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

Re: "FOR" loop to march through cells

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.