Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue with Find | Excel Discussion (Misc queries) | |||
FIND & REPLACE ISSUE | Excel Discussion (Misc queries) | |||
find and replace issue in macro | New Users to Excel | |||
Find Issue (Menu Find) | Excel Discussion (Misc queries) | |||
Formula Find Issue | Excel Programming |