Results 1 to 15 of 16
Thread: For Next Loops

20090307, 09:27 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
I think, if my approach is correct, i am trying to master the art of For Next Looping.
Outside of the vba, what I am trying to do is VLOOKUP.
2 ranges y1 and y2 which are say m2:m500 and m2:m10000
For each cell (z1) in y1, lookup y2, if exists, y1 offset 1 = found. something like:
[codebox]
For Each z1 In y1
If z1.Find(What:=y2, LookIn:=xlValues, LookAt:=xlWhole) Then
z1.Offset(0, 1) = "FOUND"
End If
Next
[/codebox]

20090307, 09:36 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
"lookup y2" makes no sense  y2 is a range according to your description. Do you mean "look up z1 in y2"? Please try to be precise!

20090307, 09:40 #3
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sorry, yes.
For each cell (z1) in y1, lookup z1 in y2, if exists, y1 offset 1 = found.

20090307, 09:49 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Try the following:
Code:For Each z1 In y1 If Not y2.Find(What:=z1, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then z1.Offset(0, 1) = "FOUND" End If Next z1

20090307, 10:04 #5
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='VegasNath' post='764057' date='07Mar2009 10:27']2 ranges y1 and y2 which are say m2:m500 and m2:m10000[/quote]
Since y1 is a subset of y2; is your code not going to find itself in each case?
I wonder if one of the following formulae in N1:N500 (inserted in N1 and filled down to N500), might be more efficient than using VBA.
=IF(ISERROR(VLOOKUP(M1,$M2:$M10000,1,FALSE)),"","F ound")
=IF(ISERROR(VLOOKUP(M1,$M$501:$M10000,1,FALSE)),"" ,"Found")Regards
Don

20090307, 10:09 #6
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, that's perfect, now to modify for the other several "lookups"
Sorry Don, I should have stated that y1 and y2 are different ranges on different sheets, but thanks for your reply.

20090307, 10:15 #7
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='VegasNath' post='764068' date='07Mar2009 11:09']Thanks Hans, that's perfect, now to modify for the other several "lookups"
Sorry Don, I should have stated that y1 and y2 are different ranges on different sheets, but thanks for your reply.[/quote]
Using VLookup across sheet boundaries should not be a problem.Regards
Don

20090307, 10:33 #8
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[codebox]
For Each z1 In y1
If Not y2.Find(What:=z1, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
'z1.Offset(0, 7) = y2.Offset(0, 9)
z1.Offset(0, 12) = "WRAP"
z1.Offset(0, 12).Interior.ColorIndex = 3
z1.Offset(0, 12).Font.ColorIndex = 0
z1.Offset(0, 12).Font.Bold = True
End If
Next z1
[/codebox]
Please see line 3: If z1 found in y2, return the value of y2 offset (0, 9) to z1.Offset(0, 7) ????

20090307, 10:50 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
[quote name='VegasNath' post='764072' date='07Mar2009 17:33'][/quote]
You're stubborn! Once again, y2 is a range, not a single cell, so
z1.Offset(0, 7) = y2.Offset(0, 9)
makes no sense. You cannot assign a whole range to a single cell! Try
Code:Dim rngFind As Range For Each z1 In y1 set rngFind = y2.Find(What:=z1, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFind Is Nothing Then z1.Offset(0, 7) = rngFind.Offset(0, 9) ...

20090307, 10:51 #10
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
I figured it out, (I THINK)
[codebox]
For Each z1 In y1
Set z2 = y2.Find(What:=z1, LookIn:=xlValues, LookAt:=xlWhole)
If Not z2 Is Nothing Then
z1.Offset(0, 7) = z2.Offset(0, 9)
z1.Offset(0, 12) = "WRAP"
z1.Offset(0, 12).Interior.ColorIndex = 3
z1.Offset(0, 12).Font.ColorIndex = 0
z1.Offset(0, 12).Font.Bold = True
End If
Next z1
[/codebox]

20090307, 12:32 #11
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Code:For Each RngFind In y1 If RngFind.Offset(0, 12) = "PURGED ITEMS" Then RngFind.Offset(0, 12).Interior.ColorIndex = 36 End If Next
RngFind.Offset(0, 12).Interior.ColorIndex = 36
to:
RngFind.Offset(0, 1):RngFind.Offset(0, 12).Interior.ColorIndex = 36
So that columns 112 are pale yellow?

20090307, 13:53 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Range(RngFind.Offset(0, 12), RngFind.Offset(0, 1)).Interior.ColorIndex = 36

20090307, 14:50 #13
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans.
Any idea what I am doing wrong with this one. I get an output for every entry in y1, regardless of the IF sratement.
Code:For Each RngFind In y1 If RngFind.Offset(0, 12) = "NON 6506 ITEMS" And RngFind.Offset(0, 5) = "HBBA 99 6506" Then y1.Offset(0, 1).Formula = "=MID(B2,11,5)" End If Next

20090307, 15:00 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
You're still stubborn! For the third time now: y1 is a multicell range, not a single cell. Try
RngFind.Offset(0, 1).Formula = "=MID(B2,11,5)"
You probably want to change the formula too  it always refers to B2 now.

20090307, 15:14 #15
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Aaaaaaaaaargh!!!! Now I am back to where I was an hour ago. This results in a #NAME?
Code:For Each RngFind In y1 If RngFind.Offset(0, 12) = "NON 6506 ITEMS" And RngFind.Offset(0, 5) = "HBBA 99 6506" Then RngFind.Offset(0, 1).Formula = "=MID(RngFind.Offset(0, 11),11,5)" End If Next