Looping a range selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

agdimitropoulos@hotmail.com - 20 Mar 2006 15:16 GMT

Hello,

I'm quite new to VBA Programming, but have used many sources to figure
out what I needed to get the job done. A lot of my problems are
format-based where I do know the logic of what I want to do but the
coding in VBA is what slows me down.

I'm trying to make a loop where I can cycle through a range of selected
cells. Here is an example of my code:

For k = 6 To 14
range("BPk:HMk").Select

'Random code

Next k

Obviously this won't work because the variable I want to change is
within quotes. What I want to do is simple yet I can't figure out the
proper coding or find what I need searching the web. Any help would be
much appreciated.

---Achilles

Reply to this Message

Ardus Petus - 20 Mar 2006 15:22 GMT

For k = 6 To 14
range("BP" & k:&":HM" & k).Select
'Random code
Next k

HTH
--
AP

> Hello,
>
[quoted text clipped - 19 lines]
>
> ---Achilles

Reply to this Message

Carim - 20 Mar 2006 15:27 GMT

Hi Achilles,
For example, looping for a given number of rows i, and for a given
number of columns j :

For i = 1 To 20
For j = 1 To 10
Cells (i,j).Select
' Perform actions you need
Next j
Next i

HTH
Carim

Reply to this Message

Carim - 20 Mar 2006 15:38 GMT

Hi Achilles,

Thanks to Ardus ' s answer, I now better understand your question ...
there is a tiny typing mistake in his answer - no ":" after the first
variable k, and only one blank space in the concatenation with & -

For k = 6 To 14
Range("BP" & k & ":HM" & k).Select
'all your programming code
Next k

HTH
Cheers
Carim

Reply to this Message

agdimitropoulos@hotmail.com - 20 Mar 2006 15:40 GMT

Thanks for the quick replies!

AP: Tried what you suggested and it did seem like the right fix I was
looking for but it gave me a compile error between '&k:&'. It was
expecting a ')' as a list separator. So I used a combination of
brackets to try and make it work but nothing.

Carim: Thought about doing this but was trying to move away from using
this format. I have been using it throughout my code but it does get
pretty huge when I have 3 or 4 variables to loop. Though, if I can't
find a one loop fix, I'll go to the multiple one you've suggested.

Thank you for the help thusfar :)

---Achilles

Reply to this Message

Achilles - 20 Mar 2006 15:45 GMT

Oh excellent! Removed the error. Many thanks for the help. Now to
test it out.

---Achilles

Reply to this Message

Achilles - 20 Mar 2006 15:51 GMT

It worked! Once again, thank you for the help.
---Achilles

Reply to this Message