MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problems with find


Posted by Lewis on November 24, 2001 2:50 PM

I am using the following to find a 5 figure number in the format 00003 in a column of numbers. It however fails to find it.

It is based on a posting by Barrie Davidson.

I would also like to restrict the search to a single column

Sub Search()
'
' Search Macro
' Macro recorded 24/11/2001 by Lewis Conquer
'
' Keyboard Shortcut: Ctrl+Shift+S
'

Sheets("Records").Select

Dim SearchValue As String
On Error Resume Next
SearchValue = InputBox("Enter RMA Number" & Chr(13) & _
"#####")
If IsError(Cells.Find(What:=SearchValue, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate) = True Then
MsgBox ("RMA Number Not Found")
End If

End Sub

Any suggestions?

Lewis


Posted by Dan on November 24, 2001 6:35 PM

Try this

This makes the search for column "A". Change as needed. HTH

Sub Search()
'
' Search Macro
' Keyboard Shortcut: Ctrl+Shift+S
'

Sheets("Records").Select

Dim SearchValue As String
On Error Resume Next
SearchValue = InputBox("Enter RMA Number" & Chr(13) & _
"#####")
Columns("A:A").Select
If IsError(Cells.Find(What:=SearchValue, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate) = True Then
MsgBox ("RMA Number Not Found")
End If
ActiveCell.Select
End Sub

Posted by Lewis on November 25, 2001 4:07 AM

Re: Try this

Search Macro Keyboard Shortcut: Ctrl+Shift+S


Thamks Ted that just highlights the column A
and still does not find the value

Posted by Lewis on November 25, 2001 4:40 AM

Search Macro Macro recorded 24/11/2001 by Lewis Conquer Keyboard Shortcut: Ctrl+Shift+S

Its to do with the number format although the numbers appear as 00003 in the worksheet the zeros are not there to the search routine. By changing the numbers to 10003 etc it works fine.
This largely gets over the problem of restricting the search to one column.

Lewis