PLS HELP! A "worksheet.MATCH " formula inside a lo

[gab]

hello, ,,,sorry if i bother again, but the las tip you gave me works excellent.....now i got an issue regarding to that...i have this code:

Dim t
For t = 1 To last_counter
t = t + 1
Windows("Frisa.xls").Activate
xc = Application.WorksheetFunction.Match(ter, Range("A:A"), 0)

....etc

I need the counter "ter" (in this case, would be = 2),find for the "ter" value (number 2) in the range resulting in the rownumber where the value of ter is.

But is not working!....when I run the code, appears an error and ter with the value of 3 (or if i change to another var, i.e just t appears 4)...so please,,i will appreciatte your help again

 

[Anthony47]

If you receive a run time error 1004 it means that the value is not found within A:A; are you sure that what you look for is there?
Anywhay you must set an error handler to avoid going to debug in the case the value is missing.
You do not specify how the variable "ter" is defined (dim) and set to a value; doublecheck that the data into ter and in A:A have the same nature (both text, for example, or both numbers...).
Also, do you have an answer to why you set ter to 2 but then it shows as 3???
Finally, I should insert a Sheets("Sheet1").select (or the sheet name you have to look in) just before the .match instruction.

Hope this helps, bye.

 

[jindon]

gab

It is always much better to specify the range with Workbook.Sheet.Range and don't Select/Activate the object.
When you use WorksheetFunction.Match, you will get Runtime error when the match doesn't exist in the range.

Dim myRng As Range
Set myRng = Workbooks("Fris.xls").Sheets("Sheet1").Range("A:A")
On Error Resume Next
xc = Application.WorksheetFunction.Match(ter, myRng, 0)
If Err <> 0 Then xc = "#N/A"
On Error GoTo 0
MsgBox xc