First, thing: What does this mean?
A column is formated with custom V-3000 when
given to me.
If that column contains text, then the custom format won't affect anything. If
the column contains just plain old numbers, you won't see things like C05565--it
would look more like V-35565 (using that custom format).
A couple things I'd check. First, I wouldn't trust Activesheet anywhere (but
I'd start here):
With ActiveSheet.Range("A:A")
would become
With workbooks("whateverthenameis").worksheets("wksnamehere").Range("A:A")
Maybe you were looking at the wrong sheet??? You did say you used Chip
Pearson's CellView Addin to check the contents and they matched character for
character (no extra spaces, no extra characters at all???).
If there were an extra space in the range to look through, like "C05565 ", then
this portion:
LookAt:=xlWhole
would cause it to fail (LookAt:=xlPart maybe a quick fix).
If that's the case, David McRitchie has some code that will remove those extra
spaces (and even extra non-breaking spaces from HTML stuff at:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")
One more warning. Just because you change the format of a cell to Text, doesn't
mean that the numeric entries are changed. It just means that the the cell will
change to text the next time you change that cell.
But that shouldn't have any affect on cells that contain: c05565.
(What do you see in the cell and what do you see in the formulabar when that
cell is selected? If it's the same, then no problem--but if there is a
difference, then maybe the cell does contain just numbers and it's the
formatting that's bothering you.)
This is my take on what you are doing. But since you activated stuff by
minimizing the active window, I could be completely wrong:
Option Explicit
Sub RosterEmployee()
Dim Z As String
Dim EmplWks As Worksheet
Dim CurWks As Worksheet
Dim oRow As Long
Dim myTable As Variant
Dim i As Long
Dim FoundCell As Range
Set CurWks = ActiveSheet 'not results???
Set EmplWks = Workbooks.Open _
(Filename:="C:\Documents and Settings\myuseraccount" _
& "\Desktop\Employee.xls").Worksheets("employeedat")
With EmplWks
.Columns("A:A").Insert
.Columns("c:c").Copy _
Destination:=.Range("a1")
.Columns("C:c").Delete
.Cells.Copy _
Destination:=CurWks.Range("a1")
End With
Z = "a1:A"
Sheets("Result").Cells.ClearContents
With ThisWorkbook.Worksheets("Lookup")
myTable = .Range(Z & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With
oRow = 0
With EmplWks.Range("A:A")
For i = LBound(myTable, 1) To UBound(myTable, 1)
Set FoundCell = .Cells.Find(What:=myTable(i, 1), _
After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
oRow = oRow + 1
FoundCell.EntireRow.Copy _
Destination:=Worksheets("Result").Range("a" & oRow)
End If
Next i
End With
End Sub
I'm at a loss. It looks to me like it's a data problem--not a code problem.
