How can I do this in a macro ?

  • Thread starter Thread starter Eric Dreshfield
  • Start date Start date
E

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 !
 
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"
 
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 !!!
 
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.
 
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
 
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
 
J.E.

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