Loop macro in selected range

S

Sjaakve

Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

Range("A1:A4,C7,D9,A11:G11").Select
Range("A11").Activate
Application.Run "Macro1"

and that several times


This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak
 
E

excel-ant

Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

    Range("A1:A4,C7,D9,A11:G11").Select
    Range("A11").Activate
    Application.Run "Macro1"

and that several times

This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Yep,

sub SelectionLoop

dim cl as selection

for each cl in Selection
--> insert code here changing cell to cl<----
next

end sub

Ant
http://www.excel-ant.co.uk
 
M

Mike H

Hi,

There are better ways than this but without seeing macro1; i wrote my own
and guessed what yours does, you could try this.


Sub standard()
For Each c In Range("A1:A4,C7,D9,A11:G11")
c.Select
Application.Run "Macro1"
Next
End Sub

Sub Macro1()
ActiveCell.Interior.ColorIndex = 3
End Sub

Mike
 
R

Ron de Bruin

If you want to loop use this

Sub Test()
Dim smallrng As Range

For Each smallrng In Range("A1:A4,C7,D9,A11:G11").Areas
With smallrng
.Interior.ColorIndex = 3
End With
Next smallrng

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
 
S

Sjaakve

That was fast.

That helps a lot. Only, the range is not always the same. Next time i
might want to select B12,G3,D2:F5 and run the macro for each cell.

Macro1() does not turn the cell red, it was just an example. I didn't
want to complicate things any further.

Is it possible the range is variable?

thanx

Sjaak
 
M

Mike H

Then us

Sub standard()
For Each c In selection
c.Select
Application.Run "Macro1"
Next
End Sub

Mike
 
D

Dave Peterson

But if Macro1 is in the same workbook project as standard, then you could just
call it:

Sub standard()
For Each c In selection
c.Select
Call Macro1
Next c
End Sub
 
S

Sjaakve

Thank you guys!

It all works like a charm.

Dave hit the jackpot.

All imaginable selections work, each cell get the required work done.

Sjaak
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top