VBA Find Issue
 

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

VBA Find Issue
 

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

--
Regards,
Tom Ogilvy


"Pete" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

VBA Find Issue
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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