How can I do this in a macro ?

[Eric Dreshfield]

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 !

 

[Eric Dreshfield]

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"

 

[Eric Dreshfield]

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

 

[steve]

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.

 

[J.E. McGimpsey]

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

 

[steve]

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

 

[Eric Dreshfield]

J.E.

Thanks...I do have it working the way I wanted it now...I
appreciate all the help !
Eric