Excel Macro required

  VNAM75 09:53 31 Jul 2006
Locked

I have a list of invoice numbers in column A Which I add to daily. I want to add a command button which when pressed gives me a prompt box asking for the invoice number. I want it to search by "wildcard" ie. if I enter 457 it takes me to the first number containing 457, then to the second containing that number etc. I know how to assign the code to the command button but dont know the VBA; recording the actions in macro recorder "select column A, Ctrl & F" doesn't work!

Additional:

Is it possible to have it coded so only the "current body" of cells is searched? eg. if I have 100 invoice numbers, A2:A101 is highlighted(searched) when the prompt box appears, and when I add another 20 entries A2:A121 would be searched.

  VoG II 18:51 02 Aug 2006

Sub Get_Invoice()
Dim iLastRow, Found As Range, tempcell As Range, MyValue
MyValue = Application.InputBox(prompt:="Enter number", Type:=1)
Set tempcell = Columns(1).Find(what:=MyValue, lookat:=xlPart)
If tempcell Is Nothing Then
MsgBox "Not found"
Exit Sub
Else
tempcell.EntireRow.Select
MsgBox "Found!"
End If
Do
Set Found = tempcell
Set tempcell = Columns(1).FindNext(after:=Found)
If tempcell.Row < Found.Row Then
MsgBox "Not found again"
Exit Sub
Else
tempcell.EntireRow.Select
MsgBox "Found again!"
End If
Loop
End Sub

  VoG II 19:06 02 Aug 2006

Sub Get_Invoice()
Dim Found As Range, tempcell As Range, MyValue
MyValue = Application.InputBox(prompt:="Enter number", Type:=1)
Set tempcell = Columns(1).Find(what:=MyValue, lookat:=xlPart)
If tempcell Is Nothing Then
MsgBox "Not found"
Exit Sub
Else
tempcell.EntireRow.Select
MsgBox "Found!"
End If
Do
Set Found = tempcell
Set tempcell = Columns(1).FindNext(after:=Found)
If tempcell.Row <= Found.Row Then
MsgBox "Not found again"
Exit Sub
Else
tempcell.EntireRow.Select
MsgBox "Found again!"
End If
Loop
End Sub


To use this, open your workbook, ALT+F11, Insert > Module, then come back here and copy the code, go back to your module and paste the code in. then close the Visual Basic Editor using the X.

This will search for entries in Column A - no matter how many there are. It has to be run from the sheet that you want to search. N.B. there is absolutely no error checking whatsoever!

Incidentally, it did not take me 3 days to code this - I've been away ;o)

  VNAM75 19:32 02 Aug 2006

Thanks VoG™, thats brilliant. I always find VBA puzzling so have never bothered tried learning it, but I can use other aspects of Excel eg. MS query, pivot tables, nested formulas etc and even sql in access...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?