Help with Lookup

[PeasNCarrots]

I have 2 worksheets.

The first one has one cell with a value in it (SLIC), say it was 2040.

Then the first col is blank. I want to be able to pull all employees last name from the other worksheet where the SLIC's are =

The next sheet has two columns, col 1 has a list of SLIC and col 2 = Emp Last name
====================================================
1st Sheet

Slic = 2040

col 1
------
I want all the data from LastName COL where SLIC from sheet 1 = SLIC from sheet 2. VLookup is not working.
------------------------------------------------------
2nd Sheet
SLIC LastName
2040 Ryan
2040 Zous
2040 Rodriguez

 

[LeighAnne]

Why not simplify things and use a data filter on sheet 2. I.e. DATA - FILTER - AUTOFILTER

Then you can filter for any SLIC code you require.

Le

 

[PeasNCarrots]

Because there are other elements involved. Worksheet 2 is creating an entirely diff report

 

[PeasNCarrots]

Never mind I just programmed it with VBA.
Sub GetEmployeeName()

Dim strSLICA, strSLICB As Integer
Dim arrEmployeeName() As String
Dim strEmployeeName As String
Dim intRowBeg As Integer
Dim intRowEnd As Integer

intRowBeg = 2
intRowEnd = 1510

Sheets("OnRoadMOP").Select
strSLICA = CInt(Range("C3").Value)
Sheets("Sheet1").Select

For i = intRowBeg To intRowEnd
strSLICB = CInt(Cells(i, 1).Value)
If strSLICB = strSLICA Then
strEmployeeName = Cells(i, 2).Value & strEmployeeName
Else
End If
Next

'Assign values to array
arrEmployeeName = Split(SuperTrim(strEmployeeName))

'Add values of array to Sheet OnRoadMOP
Sheets("OnRoadMOP").Select

For i = LBound(arrEmployeeName) To UBound(arrEmployeeName)
Cells(7 + i, 2).Value = arrEmployeeName(i)
Next
End Sub

Public Function SuperTrim(TheString As String) As String
Dim Temp As String, DoubleSpaces As String
DoubleSpaces = Chr(32) & Chr(32)
Temp = Trim(TheString)
Temp = Replace(Temp, DoubleSpaces, Chr(32))
Do Until InStr(Temp, DoubleSpaces) = 0
Temp = Replace(Temp, DoubleSpaces, Chr(32))
Loop
SuperTrim = Temp
End Function