Macro help: VLOOKUP with conditions

[uberathlete]

Hi all. Say I have a sheet (sheet 1) that has this data:

Group ID Number
LMF1_bla AAAA 11111
LMF1_blu BBBB 33456
LMF2_gh KKKK 45456
LMF2_bla AAAA 22222


And then I have another sheet (sheet 2) with this data:

ID Number
AAAA


I basically want to VLOOKUP the Number for AAAA but only if it is part of a Group that contains LMF1 in sheet 1. Can someone help me make a macro for this task? Any help would be greatly appreciated.

[Tom Ogilvy]

Sub AAAA()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3
' rng3 holds "AAAA"
Set rng3 = Worksheets("Sheet2").Range("A1")
With Worksheets("Sheet1")
Set rng = .Range(.Cells(1, 2), _
.Cells(2, 2).End(xlDown))
End With
ebug.Print rng.Address
Set rng1 = rng.Find( _
What:=rng3.Value, _
After:=rng(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
If InStr(1, rng1.Offset(0, -1), _
"LMF1", vbTextCompare) Then
If rng2 Is Nothing Then
Set rng2 = rng1
Else
Set rng2 = Union(rng2, rng1)
End If
End If
Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAddr
If Not rng2 Is Nothing Then
' now work with rng2
rng2.Parent.Activate
rng2.Select
Else
MsgBox "Nothing found"
End If
End If

End Sub

--
Regards,
Tom Ogilvy

"uberathlete" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all. Say I have a sheet (sheet 1) that has this data:
>
> Group ID Number
> LMF1_bla AAAA 11111
> LMF1_blu BBBB 33456
> LMF2_gh KKKK 45456
> LMF2_bla AAAA 22222
>
>
> And then I have another sheet (sheet 2) with this data:
>
> ID Number
> AAAA
>
>
> I basically want to VLOOKUP the Number for AAAA but only if it is part
> of a Group that contains LMF1 in sheet 1. Can someone help me make a
> macro for this task? Any help would be greatly appreciated.
>
>
> --
> uberathlete
> ------------------------------------------------------------------------
> uberathlete's Profile:
http://www.excelforum.com/member.php...o&userid=28388
> View this thread: http://www.excelforum.com/showthread...hreadid=479763
>

[uberathlete]

Thank Tom . Hmm .. unfortunately if it gives out an error in the ebug.Print rng.Address portion. Is there any way to fix this?

[uberathlete]

<please ignore>

[Tom Ogilvy]

Yes, remove that line. I guess when I went to delete it I only got the
first character. It was

Debug.Print rng.Address

but it is not needed.

--
Regards,
Tom Ogilvy

"uberathlete" <[email protected]>
wrote in message
news:[email protected]...
>
> Thank Tom . Hmm .. unfortunately if it gives out an error in the
> ebug.Print rng.Address portion. Is there any way to fix this?
>
>
> --
> uberathlete
> ------------------------------------------------------------------------
> uberathlete's Profile:
http://www.excelforum.com/member.php...o&userid=28388
> View this thread: http://www.excelforum.com/showthread...hreadid=479763
>