Looping issue

Bengo

Board Regular
Joined
Apr 14, 2010
Messages
202
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!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
[COLOR=#333333]For Each MyCell In MyRange[/COLOR]
[COLOR=#ff0000]    MyCell[/COLOR][COLOR=#333333].Formula = ...[/COLOR]
 
Last edited:

Bengo

Board Regular
Joined
Apr 14, 2010
Messages
202
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.
Code:
[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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 14, 2010
Messages
202

ADVERTISEMENT

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

thanks again
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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:
Code:
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.
 
Last edited:

Bengo

Board Regular
Joined
Apr 14, 2010
Messages
202
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,076
Messages
5,599,627
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top