VBA Find Issue

[Pete]

I have a need to lookup the value of cell A3 and compare it to a list in cell
C3:C55. If the value of cell a3 is found I want to activate the the cell
within colum C and offset the active cell by 1 colum (D) and copy that new
cell and past the answer in cell F3.

I know XL can do this, but I might be making harder than it has to be.
Any suggestions.
--
Pete

[Tom Ogilvy]

in F3
=if(iserror(Vlookup(A3,C3:D55,2,false),"",Vlookup(A3,C3:D55,2,false))

--
Regards,
Tom Ogilvy


"Pete" <[email protected]> wrote in message
news:[email protected]...
> I have a need to lookup the value of cell A3 and compare it to a list in
cell
> C3:C55. If the value of cell a3 is found I want to activate the the cell
> within colum C and offset the active cell by 1 colum (D) and copy that new
> cell and past the answer in cell F3.
>
> I know XL can do this, but I might be making harder than it has to be.
> Any suggestions.
> --
> Pete

[Bob Phillips]

On Error Resume Next
iRow = Application.Match(Range("A3").Value,Range("C3:C35"),0)
On Error Goto 0
If iRow <> 0 Then
Cells(2+iRow,"D").Copy Range("F3")
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pete" <[email protected]> wrote in message
news:[email protected]...
> I have a need to lookup the value of cell A3 and compare it to a list in
cell
> C3:C55. If the value of cell a3 is found I want to activate the the cell
> within colum C and offset the active cell by 1 colum (D) and copy that new
> cell and past the answer in cell F3.
>
> I know XL can do this, but I might be making harder than it has to be.
> Any suggestions.
> --
> Pete

[Pete]

Tom

I trust that this will work, but the reason I want to select the cell and
copy it is because I want to keep the cell formating on the answer and also
bring in some wordart that is within the cell answer.

Any suggestion now.
--
Pete


"Tom Ogilvy" wrote:

> in F3
> =if(iserror(Vlookup(A3,C3:D55,2,false),"",Vlookup(A3,C3:D55,2,false))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Pete" <[email protected]> wrote in message
> news:[email protected]...
> > I have a need to lookup the value of cell A3 and compare it to a list in
> cell
> > C3:C55. If the value of cell a3 is found I want to activate the the cell
> > within colum C and offset the active cell by 1 colum (D) and copy that new
> > cell and past the answer in cell F3.
> >
> > I know XL can do this, but I might be making harder than it has to be.
> > Any suggestions.
> > --
> > Pete
>
>
>

[Pete]

Thanks for your insight Bob, but I get a run time error 13 "Type mismatch" on
row " If iRow <> 0 Then"
--
Pete


"Bob Phillips" wrote:

> On Error Resume Next
> iRow = Application.Match(Range("A3").Value,Range("C3:C35"),0)
> On Error Goto 0
> If iRow <> 0 Then
> Cells(2+iRow,"D").Copy Range("F3")
> End If
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Pete" <[email protected]> wrote in message
> news:[email protected]...
> > I have a need to lookup the value of cell A3 and compare it to a list in
> cell
> > C3:C55. If the value of cell a3 is found I want to activate the the cell
> > within colum C and offset the active cell by 1 colum (D) and copy that new
> > cell and past the answer in cell F3.
> >
> > I know XL can do this, but I might be making harder than it has to be.
> > Any suggestions.
> > --
> > Pete
>
>
>

[Tom Ogilvy]

There is no wordart within a cell, so copying the cell will not copy any
wordart that may be located above the cell. You will have to do that
separately. You can perform these actions manually with the macro recorder
turned on to get the basic syntax.

--
Regards,
Tom Ogilvy


"Pete" <[email protected]> wrote in message
news:[email protected]...
> Tom
>
> I trust that this will work, but the reason I want to select the cell and
> copy it is because I want to keep the cell formating on the answer and
also
> bring in some wordart that is within the cell answer.
>
> Any suggestion now.
> --
> Pete
>
>
> "Tom Ogilvy" wrote:
>
> > in F3
> > =if(iserror(Vlookup(A3,C3:D55,2,false),"",Vlookup(A3,C3:D55,2,false))
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Pete" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a need to lookup the value of cell A3 and compare it to a list
in
> > cell
> > > C3:C55. If the value of cell a3 is found I want to activate the the
cell
> > > within colum C and offset the active cell by 1 colum (D) and copy that
new
> > > cell and past the answer in cell F3.
> > >
> > > I know XL can do this, but I might be making harder than it has to be.
> > > Any suggestions.
> > > --
> > > Pete
> >
> >
> >

[Tom Ogilvy]

Application.Match(Range("A3").Value,Range("C3:C35"),0)
returns an error value if A3 isn't matched in C3:C35, so irow would need to
be declared as variant.

also the test would fail:

irow = cverr(xlNA)
? irow
Error 0
? irow <> 0 '<== raises a 13 type mismatch error as well.

I think Bob wanted the version of Match that raises a trappable. error.

Sub aa()
Dim iRow As Long
On Error Resume Next
iRow = Application.WorksheetFunction _
.Match(Range("A3").Value, Range("C3:C35"), 0)
On Error GoTo 0
Debug.Print iRow
If iRow <> 0 Then
Cells(2 + iRow, "D").Copy Range("F3")
End If

End Sub


--
Regards,
Tom Ogilvy



"Pete" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your insight Bob, but I get a run time error 13 "Type mismatch"
on
> row " If iRow <> 0 Then"
> --
> Pete
>
>
> "Bob Phillips" wrote:
>
> > On Error Resume Next
> > iRow = Application.Match(Range("A3").Value,Range("C3:C35"),0)
> > On Error Goto 0
> > If iRow <> 0 Then
> > Cells(2+iRow,"D").Copy Range("F3")
> > End If
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Pete" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a need to lookup the value of cell A3 and compare it to a list
in
> > cell
> > > C3:C55. If the value of cell a3 is found I want to activate the the
cell
> > > within colum C and offset the active cell by 1 colum (D) and copy that
new
> > > cell and past the answer in cell F3.
> > >
> > > I know XL can do this, but I might be making harder than it has to be.
> > > Any suggestions.
> > > --
> > > Pete
> >
> >
> >

[Bob Phillips]

No, I wanted the application variant.

I assumed, and should have said, that the variable iRow was declared as a
Long.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Application.Match(Range("A3").Value,Range("C3:C35"),0)
> returns an error value if A3 isn't matched in C3:C35, so irow would need
to
> be declared as variant.
>
> also the test would fail:
>
> irow = cverr(xlNA)
> ? irow
> Error 0
> ? irow <> 0 '<== raises a 13 type mismatch error as well.
>
> I think Bob wanted the version of Match that raises a trappable. error.
>
> Sub aa()
> Dim iRow As Long
> On Error Resume Next
> iRow = Application.WorksheetFunction _
> .Match(Range("A3").Value, Range("C3:C35"), 0)
> On Error GoTo 0
> Debug.Print iRow
> If iRow <> 0 Then
> Cells(2 + iRow, "D").Copy Range("F3")
> End If
>
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Pete" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for your insight Bob, but I get a run time error 13 "Type
mismatch"
> on
> > row " If iRow <> 0 Then"
> > --
> > Pete
> >
> >
> > "Bob Phillips" wrote:
> >
> > > On Error Resume Next
> > > iRow = Application.Match(Range("A3").Value,Range("C3:C35"),0)
> > > On Error Goto 0
> > > If iRow <> 0 Then
> > > Cells(2+iRow,"D").Copy Range("F3")
> > > End If
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Pete" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a need to lookup the value of cell A3 and compare it to a
list
> in
> > > cell
> > > > C3:C55. If the value of cell a3 is found I want to activate the the
> cell
> > > > within colum C and offset the active cell by 1 colum (D) and copy
that
> new
> > > > cell and past the answer in cell F3.
> > > >
> > > > I know XL can do this, but I might be making harder than it has to
be.
> > > > Any suggestions.
> > > > --
> > > > Pete
> > >
> > >
> > >
>
>

[Tom Ogilvy]

I guess the assignment of the error value would not work so Irow would be
zero - my oversight. So declaring it as Long or Integer would fix the
current problem. Kind of a heavy handed approach in my opinion. But now he
has two ways to do it.

Dim iRow as Variant
iRow = Application.Match(Range("A3").Value,Range("C3:C35"),0)
If not iserror(iRow) Then
Cells(2+iRow,"D").Copy Range("F3")
End If

Now he has three.

--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> No, I wanted the application variant.
>
> I assumed, and should have said, that the variable iRow was declared as a
> Long.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Application.Match(Range("A3").Value,Range("C3:C35"),0)
> > returns an error value if A3 isn't matched in C3:C35, so irow would need
> to
> > be declared as variant.
> >
> > also the test would fail:
> >
> > irow = cverr(xlNA)
> > ? irow
> > Error 0
> > ? irow <> 0 '<== raises a 13 type mismatch error as well.
> >
> > I think Bob wanted the version of Match that raises a trappable. error.
> >
> > Sub aa()
> > Dim iRow As Long
> > On Error Resume Next
> > iRow = Application.WorksheetFunction _
> > .Match(Range("A3").Value, Range("C3:C35"), 0)
> > On Error GoTo 0
> > Debug.Print iRow
> > If iRow <> 0 Then
> > Cells(2 + iRow, "D").Copy Range("F3")
> > End If
> >
> > End Sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Pete" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks for your insight Bob, but I get a run time error 13 "Type
> mismatch"
> > on
> > > row " If iRow <> 0 Then"
> > > --
> > > Pete
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > On Error Resume Next
> > > > iRow =
Application.Match(Range("A3").Value,Range("C3:C35"),0)
> > > > On Error Goto 0
> > > > If iRow <> 0 Then
> > > > Cells(2+iRow,"D").Copy Range("F3")
> > > > End If
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Pete" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have a need to lookup the value of cell A3 and compare it to a
> list
> > in
> > > > cell
> > > > > C3:C55. If the value of cell a3 is found I want to activate the
the
> > cell
> > > > > within colum C and offset the active cell by 1 colum (D) and copy
> that
> > new
> > > > > cell and past the answer in cell F3.
> > > > >
> > > > > I know XL can do this, but I might be making harder than it has to
> be.
> > > > > Any suggestions.
> > > > > --
> > > > > Pete
> > > >
> > > >
> > > >
> >
> >
>
>