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 !

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 !
.

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 !
.

.

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 !
.

.

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 !
.

.

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 !
.

.



.

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 !
.

.

.