GOTO a Part Number
 

Hi All.....

If someone would be so kind, I am in need of a macro that will pop up a
small window asking for a Part Number to be entered, then once that is typed
in, the pop-up will have two buttons, one to "Cancell" and the other to
"GOTO" the cell in column C that has that Part Number in it.......I just
don't seem to be able to get there by "recording"......

TIA
Vaya con Dios,
Chuck, CABGx3

GOTO a Part Number
 

Hi Chuck,
Could you just use the Find dialog (menu Edit Find) ?

Regards,
Sebastien

"CLR" wrote:

Hi All.....

If someone would be so kind, I am in need of a macro that will pop up a
small window asking for a Part Number to be entered, then once that is typed
in, the pop-up will have two buttons, one to "Cancell" and the other to
"GOTO" the cell in column C that has that Part Number in it.......I just
don't seem to be able to get there by "recording"......

TIA
Vaya con Dios,
Chuck, CABGx3

GOTO a Part Number
 

Thanks for responding Sebastien........yes, that "Find Dialog" is the type of
thing I'm after, but I just wanted to set it up as a macro along with my
others but just cant seem to be able to "record" it that way, and I wanted
not to have all the other "find/replace" options available to the user at
that time........just the little "Enter a Part Number" window.

Vaya con Dios,
Chuck, CABGx3

"sebastienm" wrote:

Hi Chuck,
Could you just use the Find dialog (menu Edit Find) ?

Regards,
Sebastien

"CLR" wrote:

Hi All.....

If someone would be so kind, I am in need of a macro that will pop up a
small window asking for a Part Number to be entered, then once that is typed
in, the pop-up will have two buttons, one to "Cancell" and the other to
"GOTO" the cell in column C that has that Part Number in it.......I just
don't seem to be able to get there by "recording"......

TIA
Vaya con Dios,
Chuck, CABGx3

GOTO a Part Number
 

ok. Try:
'------------------------------------------------------------------
Sub FindPart()
Dim res
Dim RgToSearch As Range, RgFound As Range

Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Enter Part Number", "Find Part", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues,
lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "Part " & res & " not found."
Else
Application.Goto Reference:=RgFound.Address(True, True, xlR1C1)
End If

End Su
'----------------------------------------------------------------------------------

The entry also allows the use of wildcard character. For example, entering:
- AB searches for AB exactly
- AB* searches for any string strarting with AB
- AB? searchies fpr 3-caharacter strings starting with AB
- AB?? searches for 4-character string starting with AB
....

Regards,
Sebastien

"CLR" wrote:

Thanks for responding Sebastien........yes, that "Find Dialog" is the type of
thing I'm after, but I just wanted to set it up as a macro along with my
others but just cant seem to be able to "record" it that way, and I wanted
not to have all the other "find/replace" options available to the user at
that time........just the little "Enter a Part Number" window.

Vaya con Dios,
Chuck, CABGx3

"sebastienm" wrote:

Hi Chuck,
Could you just use the Find dialog (menu Edit Find) ?

Regards,
Sebastien

"CLR" wrote:

Hi All.....

If someone would be so kind, I am in need of a macro that will pop up a
small window asking for a Part Number to be entered, then once that is typed
in, the pop-up will have two buttons, one to "Cancell" and the other to
"GOTO" the cell in column C that has that Part Number in it.......I just
don't seem to be able to get there by "recording"......

TIA
Vaya con Dios,
Chuck, CABGx3

GOTO a Part Number
 

Sebastian......

Your code was EXACTLY what I wanted.......even taking it a step beyond, to
cover situatuations I had not considered.......it's perfect!!!, thank you so
very very much.

Vaya con Dios,
Chuck, CABGx3



"sebastienm" wrote:

ok. Try:
'------------------------------------------------------------------
Sub FindPart()
Dim res
Dim RgToSearch As Range, RgFound As Range

Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Enter Part Number", "Find Part", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues,
lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "Part " & res & " not found."
Else
Application.Goto Reference:=RgFound.Address(True, True, xlR1C1)
End If

End Sub
'----------------------------------------------------------------------------------

The entry also allows the use of wildcard character. For example, entering:
- AB searches for AB exactly
- AB* searches for any string strarting with AB
- AB? searchies fpr 3-caharacter strings starting with AB
- AB?? searches for 4-character string starting with AB
...

Regards,
Sebastien

"CLR" wrote:

Thanks for responding Sebastien........yes, that "Find Dialog" is the type of
thing I'm after, but I just wanted to set it up as a macro along with my
others but just cant seem to be able to "record" it that way, and I wanted
not to have all the other "find/replace" options available to the user at
that time........just the little "Enter a Part Number" window.

Vaya con Dios,
Chuck, CABGx3

"sebastienm" wrote:

Hi Chuck,
Could you just use the Find dialog (menu Edit Find) ?

Regards,
Sebastien

"CLR" wrote:

Hi All.....

If someone would be so kind, I am in need of a macro that will pop up a
small window asking for a Part Number to be entered, then once that is typed
in, the pop-up will have two buttons, one to "Cancell" and the other to
"GOTO" the cell in column C that has that Part Number in it.......I just
don't seem to be able to get there by "recording"......

TIA
Vaya con Dios,
Chuck, CABGx3