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!

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


"ed.cabrera" wrote:

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!

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

"ed.cabrera" wrote:

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!

On Aug 13, 1:45*pm, JLGWhiz wrote:
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 * *



"ed.cabrera" wrote:
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!- Hide quoted text -

- 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)

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.

"ed.cabrera" wrote:

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!

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" wrote:

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!