Find And Replace

[Steved]

Hello from Steved

Find in column C and enter Data in Column B

Highlited Col C and then using the find dialog box I type in say 748, it
finds it on row 247, now I would like to type in Col B 5478, but I need to
close the dialog box then goto the left type in Col B 5478, then start the
whole process again. How anyone out their work out a nice formula or
application that will allow to find in Col C and then Type in Data to the
left in Col B. An example would be Col C 247 and Data to be typed in Col B
247 ie 5478.

Thankyou.

 

[sebastienm]

Hi
You could use the AutoFilter instead: menu Data > Filter > AutoFilter
Select a value from the dropdown in the C col so that only these rows are
displayed, then enter data in col B, then choose a new value from the drop
down...

 

[Steved]

Hello from Steved

I believe the macro below is one off this forum

Would it be possible to develop so that in this case part find in Col C it
then
allows one to type say 1905 and place in cell Col B, as at the moment using
your macro it finds what I want which is fine. As an example the dialog box
comes up I then type in 041 in finds it in Row C567, what I would like to
happen now is I type in 1901 and it inputsit in B567. Please



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

 

[sebastienm]

in that macro, you would just have to replace the line:
Application.Goto Reference:=RgFound.Address(True, True, xlR1C1)
by
Application.Goto Reference:=RgFound.Offset(0,-1).Address(True, True,
xlR1C1)

 

[Steved]

Thankyou.

in that macro, you would just have to replace the line:
Application.Goto Reference:=RgFound.Address(True, True, xlR1C1)
by
Application.Goto Reference:=RgFound.Offset(0,-1).Address(True, True,
xlR1C1)