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

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"
wrote in message
...

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

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

--
uberathlet
-----------------------------------------------------------------------
uberathlete's Profile: http://www.excelforum.com/member.php...fo&userid=2838
View this thread: http://www.excelforum.com/showthread.php?threadid=47976

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"
wrote in message
...

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

<please ignore


--
uberathlete
------------------------------------------------------------------------
uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388
View this thread: http://www.excelforum.com/showthread...hreadid=479763