Little help with "Number stored as Text"

[James8309]

Hi, everyone

I have this Macro doing a lookup from lookupfile then return "not
found" entries into the next sheet. The Macro works beautifully
however because my lookup values in sheet1 are phone numbers stored as
text. (i.e. 0290099009) with little green triangle symbol appearing on
left top of the cell when it actually does look up and transfer to
sheet2 it just copies it as a number (i.e. 290099009).

1. How do I make it copy to next sheet2 with exact same format as in
sheet1?
2. How do I make those "Not found" results from sheet1 deleted after
copying it to sheet2?

Thank you for your help in advance!

Sub Lookup()

Lookfname = "C:\MyDocs\Report.xls"
Set lookbk = Workbooks.Open(Filename:=Lookfname)
Set SearchRange = lookbk.Sheets("BID").Columns("G")

With Workbooks("Report.xls").Sheets("Sheet1")
Sh1RowCount = 3
Sh2RowCount = 3
Do While .Range("A" & Sh1RowCount) <> ""
SearchValue = .Range("A" & Sh1RowCount)
Set c = SearchRange.Find(what:=SearchValue, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
With Workbooks("Report.xls").Sheets("Sheet2")
.Range("A" & Sh2RowCount) = SearchValue
Sh2RowCount = Sh2RowCount + 1
End With
Else
.Range("B" & Sh1RowCount) = c.Offset(0, 6)
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
lookbk.Close savechanges:=False

End Sub

 

[BobT]

To make numbers appear as text you would need to prefix with an apostrophe
('). So:

..Range("A" & Sh2RowCount) = "'" & SearchValue