Looping issue

[Bengo]

Good evening,
I am trying to loop through highlighted cells, to use the script below to anchor multiple cells within formulae:

Sub ANCHOR()

Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Selection

For Each MyCell In MyRange

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)

Next MyCell

End Sub

For some reason it works in the first cell selected but fails to loop through the other highlighted cells

Can anyone shed any light as to the reason?

Many thanks!

 

[Joe4]

That is because the "ActiveCell" never moves in your macro. You don't want to use "ActiveCell" here anyhow. You want to use "MyCell" to reference the cell that it is currently on during the loop, i.e.

[COLOR=#333333]For Each MyCell In MyRange[/COLOR]
[COLOR=#ff0000]    MyCell[/COLOR][COLOR=#333333].Formula = ...[/COLOR]

 

[Bengo]

That is because the "ActiveCell" never moves in your macro. You don't want to use "ActiveCell" here anyhow. You want to use "MyCell" to reference the cell that it is currently on during the loop, i.e.

[COLOR=#333333]For Each MyCell In MyRange[/COLOR]
[COLOR=#ff0000]    MyCell[/COLOR][COLOR=#333333].Formula = ...[/COLOR]

that's it!! thank you very much problem solved!

 

[Joe4]

You are welcome!

I usually try to avoid using ActiveCell in VBA code. Usually, you do not need to, because you usually do not need to actually select a cell to reference it or work with it.

 

[Bengo]

Makes sense, I'll bear that in mind for future ones

thanks again

 

[Joe4]

You are welcome again.

Here is one more tip, pursuant to my last point.

If you use the Macro Recorder a lot, you may see a lot of recorded code like this:

Range("A1").Select
ActiveCell.FormulaR1C1 = "abc"

Whenever you have one line that ends with ".Select" and the next begins with "ActiveCell." or "Selection.", you can usually combine those two rows like this:

Range("A1").FormulaR1C1 = "abc"

Note only does it shorten the code, but it also makes it run faster, as Select statements slow down your code.

 

[Bengo]

You're right I've always done that as I think I learned through recording manually and saw just that. I need to work more with defined ranges and get away from that!