Numbers are text and won't match using a formula

[Bud]

Hello

Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel....This formula moves the
personnel number fro the SAPTasks worksheet into another worksheet containing
the formula.
Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br,
"b"))...this formula than fills that formula down a number of rows that is
contained in the field br
Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000,0))))"...this
formula gets the name of the person from the worksheet personnel by matching.

The issue is the personnel number from the SAPTasks worksheet is TEXT so it
isn't finding the match.

I just want to be able to find the name.

Excel suggested multiplying by 1 in the field containing the number(Text). I
recorded a macro that works and gives teh following but it goes 65536 or
something.

How can I do the following code better so it converts the number to a number
and allows the matching on a designated number of rows in br??????
Columns("A:A").Select
Selection.Copy
Columns("Q:Q").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("R1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Range("R1").Select
Application.CommandBars("Task Pane").Visible = False
Range("R1").Select
Selection.Copy
Columns("Q:Q").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Columns("Q:Q").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

 

[OssieMac]

Hi Bud,

Try the following. See comments for what it is doing. Not the way the
purists would do it but i think that it might be easier for you to understand.

Sub Macro1()
'Ensure that column A is formated to numeric
Columns("A:A").Select
Selection.NumberFormat = "0"

'Select and enter 1 in an empty cell
Range("R1").Select
ActiveCell.FormulaR1C1 = "1"

'Copy the cell
Selection.Copy

'Select from first cell to last used cell in column A
'Identifying last used cell is like selecting the
'last cell in column A (65536 or something.)
'Holding the Ctrl key and pressing up arrow to
'find last used cell in the column.
Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select

'Paste Special multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End Sub