For each query

[davidnew3754]

I'm trying to use the for each next loop to test whether the value of a cell in a range = "Y". If it does I want it to run firstpart however regardless of whether each cell = "Y" or not it runs firstpart.

Can anyone help me on this?

Sub Entireprocess()
Dim autrnl As Range
Set autrnl = Range("P100:P200")
For Each cell In autrnl
If cell.Value = "Y" Then
firstpart
End If
Next cell

Many thanks

David

 

[JackDanIce]

Can't see anything too obvious, maybe define cell as range and force a text comparison of the first character? i.e.

Sub Entireprocess()

Dim autrnl As Range, cell as Range
Set autrnl = Range("P100:P200")

Application.ScreenUpdating = False

For Each cell In autrnl
  If Ucase(Left(Cell.Value, 1)) = "Y" Then Call firstpart
Next cell

Application.ScreenUpdating = True

Set autrnl = Nothing: Set Cell = Nothing
End Sub

 

[Norie]

Do you want to run firstpart only if every cell in the range has 'Y' in it?

 

[davidnew3754]

Do you want to run firstpart only if every cell in the range has 'Y' in it?

Hi Norie

Yes that's correct, only run if it has a Y. Otherwise it should move onto the next cell in the range and exit sub at the last cell.

 

[Norie]

I might not have phrased that correctly.

Do you want to only run firstpart if all the cells contain Y?

So it would only be run once after you've checked each cell.

If you want it to run for every cell with a Y that's what your original code is doing.

 

[davidnew3754]

Thanks Norie

I'd like it to run everytime there is a cell with a Y. When I try using messageboxes rather than calling a macro it seems to work ok. I think the problem may lie in how I activate the cell when it comes across a "Y". I've copied the code for the macro which gets called. The problem here is that it offsets to the first cell in the range rather than the cell where a "Y" occurs.

[Sub firstpart()
ActiveCell.Offset(0, -12).Select
Selection.Copy
AppActivate ("Windows Internet Explorer")
AppActivate ("Windows Internet Explorer")
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 50, 45
mouse_event &H2, 0, 0, 0, GetMessageExtraInfo()
mouse_event &H4, 0, 0, 0, GetMessageExtraInfo()
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 280, 45
mouse_event &H2, 0, 0, 0, GetMessageExtraInfo()
mouse_event &H4, 0, 0, 0, GetMessageExtraInfo()
Application.Wait (Now + TimeValue("00:00:02"))
SendKeys ("insurer p")/CODE]




[QUOTE="Norie, post: 3257403, member: 26558"]I might not have phrased that correctly.

Do you want to only run firstpart if [B]all[/B] the cells contain Y?

So it would only be run once after you've checked each cell.

If you want it to run for every cell with a Y that's what your original code is doing.[/QUOTE]

 

[davidnew3754]

Bit more info here

Sub Entireprocess12()
Dim autrnl As Range, cell As Range
Set autrnl = Range("P1:P200")
For Each cell In autrnl
  If cell.Value = "Y" Then cell.Offset(0, -12).Activate
  Selection.Copy
Next
firstpart

If I place the macro outside of the loop it runs fine but only on the last cell in the range which meets the criteria.

For some reason the for each statement won't work if it's inside of Next.

 

[Norie]

Are you 100% sure it's the loop that's causing the problem?

Perhaps it's actually the sub you are calling.