Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
Here's my situation: I have a value in cell L2, that I
want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
Ok....nevermind...I figured it out. Here's how I did it.
Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
Well....so I still need someone to help me out. The code
I have shown below does work great, but after that runs, I need to basically execute the same code, but look for the value that is in cell "K2" and replace column A for that row with "PC". When I copied the code below and changed "L2" to "K2" and executed the macro I now get: Run-time error '91': Object variable or With block variable not set When I choose the debug button, the highlighted row of code is the newest one just written...the one where I am searching column I for the value in "K2". What does that mean ? What am I doing wrong....what am I missing ? Thanks !!! -----Original Message----- Ok....nevermind...I figured it out. Here's how I did it. Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
Eric,
Not sure but try this mode to your code and see if it helps Dim x as Long x = Columns("I:I").Find(what:=range("L2"), After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).ROW Cells(x,1) = "VD" this avoids selecting. Also you should add an error check in case there is no match in column I. -- sb "Eric Dreshfield" wrote in message ... Well....so I still need someone to help me out. The code I have shown below does work great, but after that runs, I need to basically execute the same code, but look for the value that is in cell "K2" and replace column A for that row with "PC". When I copied the code below and changed "L2" to "K2" and executed the macro I now get: Run-time error '91': Object variable or With block variable not set When I choose the debug button, the highlighted row of code is the newest one just written...the one where I am searching column I for the value in "K2". What does that mean ? What am I doing wrong....what am I missing ? Thanks !!! -----Original Message----- Ok....nevermind...I figured it out. Here's how I did it. Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
It means the value in K2 was not found, so there's no range for
..Select to select. You almost never need to select or activate a range in order to work with it. Using the range object directly makes your code smaller, faster and, IMO, easier to maintain: Dim found As Range Set found = ActiveSheet.Columns("I:I").Find( _ what:=Range("L2").Value, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not found Is Nothing Then found.End(xlToLeft).Value = "VD" Else MsgBox "Value in cell L2 was not found" End If In article , "Eric Dreshfield" wrote: Well....so I still need someone to help me out. The code I have shown below does work great, but after that runs, I need to basically execute the same code, but look for the value that is in cell "K2" and replace column A for that row with "PC". When I copied the code below and changed "L2" to "K2" and executed the macro I now get: Run-time error '91': Object variable or With block variable not set When I choose the debug button, the highlighted row of code is the newest one just written...the one where I am searching column I for the value in "K2". What does that mean ? What am I doing wrong....what am I missing ? Thanks !!! -----Original Message----- Ok....nevermind...I figured it out. Here's how I did it. Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
Eric,
I left out a line continuation symbol ( _ ). Have to watch out for word wrap in this forum (an in VBA). Sorry... Dim x As Long x = Columns("I:I").Find(What:=Range("L2"), After:= _ ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row MsgBox x -- sb "Eric Dreshfield" wrote in message ... Steve, Thanks for the suggestion....unfortunately, I can't seem to get that to work either. When I remove all my coding and try yours, I get the same '91' error as before. When I remove only my 2nd "replace" section (the one looking at "K2") I then get Run-time error '1004' - Unable to get the Find property of the Range class...with the debugger stopping on the "x=" new line of code. I'll keep playing around with it. Thanks. Eric -----Original Message----- Eric, Not sure but try this mode to your code and see if it helps Dim x as Long x = Columns("I:I").Find(what:=range("L2"), After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).ROW Cells(x,1) = "VD" this avoids selecting. Also you should add an error check in case there is no match in column I. -- sb "Eric Dreshfield" wrote in message ... Well....so I still need someone to help me out. The code I have shown below does work great, but after that runs, I need to basically execute the same code, but look for the value that is in cell "K2" and replace column A for that row with "PC". When I copied the code below and changed "L2" to "K2" and executed the macro I now get: Run-time error '91': Object variable or With block variable not set When I choose the debug button, the highlighted row of code is the newest one just written...the one where I am searching column I for the value in "K2". What does that mean ? What am I doing wrong....what am I missing ? Thanks !!! -----Original Message----- Ok....nevermind...I figured it out. Here's how I did it. Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this in a macro ?
J.E.
Thanks...I do have it working the way I wanted it now...I appreciate all the help ! Eric -----Original Message----- It means the value in K2 was not found, so there's no range for ..Select to select. You almost never need to select or activate a range in order to work with it. Using the range object directly makes your code smaller, faster and, IMO, easier to maintain: Dim found As Range Set found = ActiveSheet.Columns("I:I").Find( _ what:=Range("L2").Value, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not found Is Nothing Then found.End(xlToLeft).Value = "VD" Else MsgBox "Value in cell L2 was not found" End If In article , "Eric Dreshfield" wrote: Well....so I still need someone to help me out. The code I have shown below does work great, but after that runs, I need to basically execute the same code, but look for the value that is in cell "K2" and replace column A for that row with "PC". When I copied the code below and changed "L2" to "K2" and executed the macro I now get: Run-time error '91': Object variable or With block variable not set When I choose the debug button, the highlighted row of code is the newest one just written...the one where I am searching column I for the value in "K2". What does that mean ? What am I doing wrong....what am I missing ? Thanks !!! -----Original Message----- Ok....nevermind...I figured it out. Here's how I did it. Columns("I:I").Select Selection.Find(what:=range("L2"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select Selection.End(xlToLeft).Select ActiveCell.Formula = "VD" -----Original Message----- Here's my situation: I have a value in cell L2, that I want to seach for in Column I. Once I find that value in column I, I want to replace the value in column A (in the same row as the value was found in column I) with a different value. My question is this: how do I recreate the search process in a macro and how can I tell it to search for the value of cell L2. Once I find where that is in column I, I can use activecell.offset to get to column A to do my replace. Thanks ! . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |