Hello from Steved

Below is giving me a compile error.

The below macro objective is to bring up an input box, I enter an input say
009 it then searches Column C:C once found say on Col C Row 238 it then
invites me to type 4567 which puts it the Col B Row 238.

What Have I not got right please. Thankyou.

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues,

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.Offset(0, -1).Address(True, True,
xlR1C1)
End If

End Sub

Your code works for me, once the line wrap problem was taken care
of.

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)

Note the _ character (preceeded by a space) at the end of the
first line of text.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steved" wrote in message
...
Hello from Steved

Below is giving me a compile error.

The below macro objective is to bring up an input box, I enter
an input say
009 it then searches Column C:C once found say on Col C Row 238
it then
invites me to type 4567 which puts it the Col B Row 238.

What Have I not got right please. Thankyou.

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues,

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.Offset(0, -1).Address(True, True,
xlR1C1)
End If

End Sub

Thankyou very much Chip.

"Chip Pearson" wrote:

Your code works for me, once the line wrap problem was taken care
of.

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)

Note the _ character (preceeded by a space) at the end of the
first line of text.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steved" wrote in message
...
Hello from Steved

Below is giving me a compile error.

The below macro objective is to bring up an input box, I enter
an input say
009 it then searches Column C:C once found say on Col C Row 238
it then
invites me to type 4567 which puts it the Col B Row 238.

What Have I not got right please. Thankyou.

Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues,

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.Offset(0, -1).Address(True, True,
xlR1C1)
End If

End Sub