Replace with an Offset?
 

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!

Replace with an Offset?
 

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

--
HTH...

Jim Thomlinson


"Steve" wrote:

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!

Replace with an Offset?
 

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" wrote in message
oups.com...
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!

Replace with an Offset?
 

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?

On Sep 12, 9:38 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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

--
HTH...

Jim Thomlinson



"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -

Replace with an Offset?
 

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"
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Steve" wrote:

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!

Replace with an Offset?
 

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


"Steve" wrote:

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?

On Sep 12, 9:38 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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

--
HTH...

Jim Thomlinson



"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -

Replace with an Offset?
 

Thanks so much, Jim!!

On Sep 12, 10:02 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"Steve" wrote:
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?

On Sep 12, 9:38 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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

--
HTH...

Jim Thomlinson

"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -