For Each Worksheet and For Each Cell

[ThomJenkins]

Hi all, appreciate any help you can offer

On a regular basis I receive a file with multiple worksheets, each with a group of cells that I want to perform a simple conversion. Although I haven't used a For Each loop before, I get the idea and thought this would be the best way to go.

The code does everything I want... and more - it converts the final sheet twice! As I'm new to the loops I'm expecting something obvious that I've missed.

Thanks in advance for your help and time.
TJ

Code:
Dim wksht As Worksheet
Dim cell As Range
Dim XRate As Currency

XRate = 1.6
Application.ScreenUpdating = False

For Each wksht In Worksheets
wksht.Activate
'Selection of cells goes here....
Range("D5:E15").Select

For Each cell In Selection
cell.Value = cell.Value / XRate
Next cell
Range("A1").Select

Next wksht

 

[cstimart]

Worked fine for me.

 

[ThomJenkins]

It does work fine! Not sure where I was coming from!

Thx

 

[ThomJenkins]

It works absolutely fine, unless.... I run it in Step mode, and then bizarelly it runs the last sheet twice (glad I wasn't going out of my mind!).

 

[HalfAce]

Hi Thom,
Can you show us the real code for this section of your posted example?

[FONT=Courier New]wksht.Activate
'Selection of cells goes here....
Range("D5:E15").Select[/FONT]

Whatever you're doing with this range, it's most likely you don't need to be selecting to do it and (even though it's a pretty small range) your code will run quicker if you don't.
What I'm thinking probably won't speed up this particular routine much (because of the small ranges in question) but it's this statement you made: "I haven't used a For Each loop before" that makes me think - if you want to learn looping (which, for the most part isn't really all that efficient) then you should probably learn a more efficient way to do it.

You interested?

 

[ThomJenkins]

You interested?

Sure I am... I haven't created the actual selection code yet, but I assume Range("d5", ActiveCell.End(xlDown).Offset(0, 1)).Select will work with the files I receive.

Some more info on the original, non-existant, problem. I've just put a counter into place, and the counter is higher than the number of cells it needs to convert. With further investigation I think a hidden sheet was causing the issue.

 

[HalfAce]

Should work fine to select the range, yes.
My point was that you probably don't need to select them in the first place.
Here's an example. (3 routines that end with the same results. One's just noticably quicker.)

Selecting and looping through the selected range (changing one cell at a time):

Sub Demo1()
Range("d5", ActiveCell.End(xlDown).Offset(0, 1)).Select
For Each cell In Selection
    cell.Font.ColorIndex = 3
Next cell
End Sub

Selecting and working the entire range in one shot:

Sub Demo2()
Range("d5", ActiveCell.End(xlDown).Offset(0, 1)).Select
Selection.Font.ColorIndex = 3
End Sub

Just specifying and working the entire range - without ever selecting anything:

Sub Demo3()
Range("d5", ActiveCell.End(xlDown).Offset(0, 1)).Font.ColorIndex = 3
End Sub

See the difference(s)?
It's all just a matter of speed & efficiency.
Hope it helps.

 

[ThomJenkins]

Thanks HalfAce,

I understand that, where possible, not selecting the cell is significantly quicker.

I think for what I'm trying, I will need to select the cells, in order to perform the calculation. Technically, I could perform a Paste Special, Divide, but for futureproofing, and ease of reading I think the loop is better - unless you have another suggestion.

Thanks again