Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
macro to search and replace with offset | Excel Discussion (Misc queries) |