Replace with an Offset?

[Steve]

Hello. Is it possible to scan column A for a particular item, and if
it finds it, replace the Value in Column C with something esle? I
know I can do this with an if statement and the use of a helper
column. Is it possible without the use of a helper column? Thanks!

 

[Guest]

This should be close. It searches Column A for "This" and places "Tada" in
column C whereever it was found...

Sub ReplaceStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Didn't Find Anything."
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offset(0, 3).Value = "Tada"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

 

[PCLIVE]

One way:

Sub test()

For Each cell In Range("A1:A100")
If cell.Value = "YourItem" _
Then
cell.Offset(0, 2).Value = "SomethingElse"
Else
End If
Next cell

End Sub



Regards,
Paul

 

[Steve]

Perfect. Thanks so much Jim. Can I ask for one slight variation to
help solve another problem I have? When it finds "This" in Column A,
can it place "Tada" in Columns C:E and J:K whereever it was found?

 

[Guest]

Oops I offset a bit to far and changed column D instead of C... Change the
offset to a 2 from a 3

rngFound.Offset(0, 3).Value = "Tada"
Should be
rngFound.Offset(0, 2).Value = "Tada"

 

[Guest]

Try this...

Sub ReplaceStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Didn't Find Anything."
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offset(0, 2).Resize(, 3).Value = "Tada"
rngFound.Offset(0, 9).Resize(, 2).Value = "Tada"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

 

[Steve]

Thanks so much, Jim!!

Try this...

Sub ReplaceStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Didn't Find Anything."
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offset(0, 2).Resize(, 3).Value = "Tada"
rngFound.Offset(0, 9).Resize(, 2).Value = "Tada"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub
--
HTH...

Jim Thomlinson





- Show quoted text -