Little help with "Number stored as Text"

J

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
 
B

BobT

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top