Add a name based on a number

[ed.cabrera]

I might be pressing my luck here ....but (I already got one really
good answer off here today ; )

Ok here's what I'm trying to figure out now:

Spreadsheet 1 contains the client's name in column A and their
employee number in column B

Spreadsheet 2 contains raw data that only lists the client's employee
number in column E.

Is there a simple (or macro) solution that can run in Spreadsheet 2
and compare what's in column E to what's in column B in Spreadsheet 1;
and then enter the client's name in column D of Spreadsheet 2?

Thanks for the help, everyone!

 

[Office_Novice]

Try this.

Option Explicit
Sub Do_Eds_Stuff()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ElastRow As Long
Dim MyRange As Range
Dim i As Variant
Dim x As Integer
Dim FoundCell As Range

Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = ActiveWorkbook.Worksheets(2)
ElastRow = ws2.Cells(Cells.Rows.Count, "E").End(xlDown).Row
Set MyRange = ws2.Range("E1:E" & ElastRow)
x = 1

For Each i In MyRange
With ws1.Range("A:A")
.Find (i)
End With
Set FoundCell = ws1.Range("A:A").Find(i)
If FoundCell Is Nothing Then
Exit Sub
ElseIf FoundCell Is FoundCell Then
ws2.Cells(x, 4).Value = FoundCell.Offset(0, 1).Value
End If
x = x + 1
Next
End Sub

 

[JLGWhiz]

This is untested:

Sub RetClient()
Dim lr1 As Long, lr2 As Long
Dim empNr As Range, c As Range
lr1 = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
lr2 = Worksheets(2).Cells(Rows.Count, 5).End(xlUp).Row
Set rng1 = Sheets(1).Range("B2:B" & lr1)
Set rng2 = Sheets(2).Range("E2:E" & lr2)
For Each empNr In rng2
Set c = rng1.Find(empNr, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
empNr.Offset(0, -1) = c.Offset(0, -1).Value
End If
Next
End Sub

 

[JLGWhiz]

Be sure your numbers are formatted the same in the emp number range on both
sheets or you will probably get a type mismatch. i.e. If one is formatted as
a string and the other is a number.

 

[Office_Novice]

Tested and debugged:

Option Explicit
Sub Do_Eds_Stuff()

Dim ws1, ws2 As Worksheet
Dim MyRange, FoundCell As Range
Dim i, x As Variant
Dim ElastRow, Alastrow As Long

Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = ActiveWorkbook.Worksheets(2)
Alastrow = ws1.Cells(ws1.Cells.Rows.Count, "A").End(xlUp).Row
ElastRow = ws2.Cells(ws2.Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange = ws2.Range("E1:E" & ElastRow)
x = 1

For Each i In MyRange
ws1.Range("A1:A" & Alastrow).Find (i)
Set FoundCell = ws1.Range("A1:A" & Alastrow).Find(i)
If FoundCell Is Nothing Then
Exit Sub
ElseIf FoundCell = i Then
ws2.Cells(x, 4).Value = FoundCell.Offset(0, 1).Value
End If
x = x + 1
Next
End Sub

 

[ed.cabrera]

This is untested:

Sub RetClient()
   Dim lr1 As Long, lr2 As Long
   Dim empNr As Range, c As Range  
   lr1 = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
   lr2 = Worksheets(2).Cells(Rows.Count, 5).End(xlUp).Row
   Set rng1 = Sheets(1).Range("B2:B" & lr1)
   Set rng2 = Sheets(2).Range("E2:E" & lr2)
   For Each empNr In rng2
      Set c = rng1.Find(empNr, LookIn:=xlValues, _
       LookAt:=xlWhole, MatchCase:=False)
        If Not c Is Nothing Then
           empNr.Offset(0, -1) = c.Offset(0, -1).Value
        End If
   Next
End Sub    










- Show quoted text -

This one worked!!

Thanks everyone for helping out! (Now if I can just figure out the
code so that I'll understand why it works LOL)