VBA Match 2 Fields

[rpaulson]

Some how I'm making this hard.

trying to use VBA only


I have a master list of Names on sheet "Member", First name in Column C, Last name in Column D, ID number in Column A

I have a second sheet "Sheet2" with combined names in Column I. ex: Bill Gates

I need to loop through the names in Sheet 2 and get the appropriate ID number from Member Sheet.

code so far:

...
set rs = worksheets("Master")
set myRange = worksheets("Sheet2").Range("I2:I500")
for each myCell in myRange
first = split(myCell.value, " ") (0)
last= split(myCell.value, " ") (1)

id =???

...

i've tried multiple index matches and vlookups and elvautate but no luck.

Like
Id=index.rs(Range("A:A"),Match(First & Last,rs.Range("C:D",0))

but no success.

Any Ideas?

thanks,

Ross

 

[Fluff]

How about

Sub copydata()

   Dim Cl As Range
   Dim ValU As String
   Dim WsM As Worksheet
   Dim Ws2 As Worksheet
   
   Set WsM = Sheets("Members")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In WsM.Range("D2", WsM.Range("D" & Rows.Count).End(xlUp))
         ValU = Cl.Value & " " & Cl.Offset(, -1).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Ws2.Range("I2", Ws2.Range("I" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[rpaulson]

Fluff,

Having trouble wrapping my head around that one, Ill see if I can incorporate that my my code. my not sure how to get the ID number as I need to do some other stuff with it later in the code.

 

[Fluff]

If you have any problems, let me know.

 

[rpaulson]

Fluff,

I'm having trouble.

I uploaded a small sample file.

Here.

http://www.computermaninc.net/GetID.xlsm

thanks for looking.

 

[Fluff]

It would help & read your OP properly!
try this

Sub copydata()

   Dim Cl As Range
   Dim ValU As String
   Dim WsM As Worksheet
   Dim Ws2 As Worksheet
   
   Set WsM = Sheets("Members")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In WsM.Range("C2", WsM.Range("C" & Rows.Count).End(xlUp))
         ValU = Cl.Value & " " & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, -2).Value
      Next Cl
      For Each Cl In Ws2.Range("I2", Ws2.Range("I" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 14).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[rpaulson]

Success, thank you very much.

 

[Fluff]

Your welcome