Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help with "Number stored as Text"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help with "Number stored as Text"
To make numbers appear as text you would need to prefix with an apostrophe
('). So: ..Range("A" & Sh2RowCount) = "'" & SearchValue "James8309" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I ignore the "Number stored as text" error message permane. | Excel Discussion (Misc queries) | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming | |||
Column listed as "Number Stored as Text" - Exported from Access | Excel Programming | |||
unable to get original cell value from a "number stored as text" | Excel Programming |