Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: For Next Loops

  1. #1
    Bronze Lounger
    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]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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!

  3. #3
    Bronze Lounger
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  5. #5
    Silver Lounger
    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='07-Mar-2009 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

  6. #6
    Bronze Lounger
    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.

  7. #7
    Silver Lounger
    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='07-Mar-2009 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

  8. #8
    Bronze Lounger
    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) ????

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='764072' date='07-Mar-2009 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)
    	...

  10. #10
    Bronze Lounger
    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]

  11. #11
    Bronze Lounger
    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
    How can I syntax this line:

    RngFind.Offset(0, -12).Interior.ColorIndex = 36

    to:

    RngFind.Offset(0, -1):RngFind.Offset(0, -12).Interior.ColorIndex = 36

    So that columns 1-12 are pale yellow?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Range(RngFind.Offset(0, -12), RngFind.Offset(0, -1)).Interior.ColorIndex = 36

  13. #13
    Bronze Lounger
    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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're still stubborn! For the third time now: y1 is a multi-cell 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.

  15. #15
    Bronze Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •