Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - update the cell to the left after a find
Hello,
I have a macro that I need to find a certain cell and update the cell to the right of that one with a certain value. My Macro is below; it currently finds the record for Gregory T Pattens. I then want it to update the cell directly to the right of this one with the value "n/a". Gregory T Pattens will not consistently be in the same location, so I can't have it locked down to always update a specific cell. I had thought that the RC[1] would move to the right but instead it's overwriting Gregory T Patten's name with "RC[1]=""n/a""" Thank you!!! Sub Macro4() ' Macro4 Macro ' Macro recorded 4/12/2007 by Jen Frost ' Columns("B:B").Select Selection.Find(What:="Pattins,Gregory T", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "RC[1]=""n/a""" End Sub -- Thank you! - Jennifer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - update the cell to the left after a find
Use this instead:
ActiveCell.Offset(0,1) = "n/a" Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - update the cell to the left after a find
Hi Jennfier,
Try it this way: ActiveCell.Offset(0, 1).Value = "n/a" -- Hope that helps. Vergel Adriano "Jennifer Cali" wrote: Hello, I have a macro that I need to find a certain cell and update the cell to the right of that one with a certain value. My Macro is below; it currently finds the record for Gregory T Pattens. I then want it to update the cell directly to the right of this one with the value "n/a". Gregory T Pattens will not consistently be in the same location, so I can't have it locked down to always update a specific cell. I had thought that the RC[1] would move to the right but instead it's overwriting Gregory T Patten's name with "RC[1]=""n/a""" Thank you!!! Sub Macro4() ' Macro4 Macro ' Macro recorded 4/12/2007 by Jen Frost ' Columns("B:B").Select Selection.Find(What:="Pattins,Gregory T", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "RC[1]=""n/a""" End Sub -- Thank you! - Jennifer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - update the cell to the left after a find
Give this a whirl. Your existing code will bomb if Greg is not found. This
code will be just fine... Sub Macro4() ' Macro4 Macro ' Macro recorded 4/12/2007 by Jen Frost Dim rngFound As Range Set rngFound = Columns("B:B").Find(What:="Pattins,Gregory T", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then rngFound.Offset(0, 1).Value = "n/a" End Sub -- HTH... Jim Thomlinson "Jennifer Cali" wrote: Hello, I have a macro that I need to find a certain cell and update the cell to the right of that one with a certain value. My Macro is below; it currently finds the record for Gregory T Pattens. I then want it to update the cell directly to the right of this one with the value "n/a". Gregory T Pattens will not consistently be in the same location, so I can't have it locked down to always update a specific cell. I had thought that the RC[1] would move to the right but instead it's overwriting Gregory T Patten's name with "RC[1]=""n/a""" Thank you!!! Sub Macro4() ' Macro4 Macro ' Macro recorded 4/12/2007 by Jen Frost ' Columns("B:B").Select Selection.Find(What:="Pattins,Gregory T", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "RC[1]=""n/a""" End Sub -- Thank you! - Jennifer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER | Excel Discussion (Misc queries) | |||
Excel 2K3 Macro - Find and Update | Excel Worksheet Functions | |||
Find LARGE, and th cell 3 cells to the left? | Excel Worksheet Functions | |||
Find and Update Macro | Excel Discussion (Misc queries) | |||
Timestamp cell to left after update | Excel Discussion (Misc queries) |