Input box, find number and go to cell
 

Hi
Can someone help with a macro doing the following?

1. Open an Inputbox (User then writes a number with four digits)
2. Search for the number in column A (from start to end. The number will
will be present only once)
3. Go to the cell with the found number
4. If the number is not found a message box should pop up telling this.

Thank you
Kaj Pedersen

Input box, find number and go to cell
 

Try...

Sub FindNumber()
Dim NumToFind, rng As Range

NumToFind = Application.InputBox("Enter the number to find", Type:=1)
If NumToFind = False Then Beep: Exit Sub

Set rng = Range("A:A").Find(NumToFind, LookIn:=xlValues)
If rng Is Nothing Then
MsgBox "The number was not found"
Else
ActiveWindow.ScrollRow = rng.Row
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Input box, find number and go to cell
 

Hi
Thank you for your suggestion that (almost) seems to work.
Still, there is a little problem:

Let's say I have the following numbers in column A

4444
404
43
4

If I carry out a search for the number "4" the macro goes to the first cell
containing the digit "4"
In this instance 4444

Can this issue be solved and also, is it possible to have the search result
as active cell?

Regards,
Kaj Pedersen

Input box, find number and go to cell
 

Hi
Thank you for your suggestion that (almost) seems to work.
Still, there is a little problem:

Let's say I have the following numbers in column A

4444
404
43
4

If I carry out a search for the number "4" the macro goes to the first cell
containing the digit "4"
In this instance 4444

Can this issue be solved and also, is it possible to have the search result
as active cell?

Regards,
Kaj Pedersen

Sub FindNumber()
Dim NumToFind, rng As Range

NumToFind = Application.InputBox("Enter the number to find", Type:=1)
If NumToFind = False Then Beep: Exit Sub

Set rng = Range("A:A").Find(NumToFind, LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "The number was not found"
Else
ActiveWindow.ScrollRow = rng.Row
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Input box, find number and go to cell
 

Thank you very much indeed.
Now only one wish is left.
Is it possible to write the macro so that the cell that was searched for is
selected
when the macro has finished?

I very much like the scroll to the top of the sheet (ActiveWindow.ScrollRow
= rng.Row),
so this must not be let out.

Thank you.

Best regards,
Kaj Pedersen

Input box, find number and go to cell
 

Ok...

Sub FindNumber()
Dim NumToFind, rng As Range

NumToFind = Application.InputBox("Enter the number to find", Type:=1)
If NumToFind = False Then Beep: Exit Sub

Set rng = Range("A:A").Find(NumToFind, LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "The number was not found"
Else
ActiveWindow.ScrollRow = rng.Row: rng.Select
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Input box, find number and go to cell
 

Hi Garry

You have now solved the problem to my full satisfaction.
Once again, thank you.

Kaj Pedersen

Input box, find number and go to cell
 

Hi Garry

You have now solved the problem to my full satisfaction.
Once again, thank you.

Kaj Pedersen

Glad to help and I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion