1. 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. "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. Sorry, yes.
For each cell (z1) in y1, lookup z1 in y2, if exists, y1 offset 1 = found.

4. 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. [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")

6. 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. [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.

8. [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. [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. 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. 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. Range(RngFind.Offset(0, -12), RngFind.Offset(0, -1)).Interior.ColorIndex = 36

13. 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. 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. 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 Last

#### Posting Permissions

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