Trying to get worksheefunction match to work

[NigelVII]

Hello,

I have this:

Dim MatchResponse as Variant
Dim PasteRow as Long
Dim nCount as Long

MatchResponse = Application.Match(Range(PasteRow, 1), _
Range(Cells(-nCount, 3), Cells(-nCount, 29)), 0)

The active cell is at (PasteRow,2).

The values in the local window are:
PasteRow = 16
nCount = 15

I am trying to then use this to do an IsError(MatchResponse) but I keep
getting a runtime error ('1004' application-defined or object-defined error)
at this statement.

I have been trying to get this to work for sometime but have reached the end
of by beginners abilities. If someone could offer assistance, it would be
appreciated.

Thanks!

 

[Joel]

VBA will catch the fact that the row number is negative and won't even run
the instruction. You probably want to put an error in a cell by dividing by
zero or fail a lookup() with N/A.

 

[NigelVII]

Thank you Joel!

I am going to change the -ncount to a positive row reference rather than a
negative offset. I'll see how that works.

Otherwise - should how I have the ranges defined work?

Thanks again.

 

[NigelVII]

Removing -ncount with a positive row reference did not work.

I get another runtime error - 1004 Method 'Range' of object' - Global' failed

Back to the drawing board. . .

 

[NigelVII]

Okay changed the first range definition (what I am trying to match).

The final statement looks like this and works for my purposes.

MatchResponse = Application.Match(Range("A" & PasteRow), Range(Cells(x, 3),
Cells(x, 29)), 0)

Can't say I understand why one range def worked and the other didn't, but
hey this works and I can move on.

My apologies for learning while doing on the forum, but sometimes it helps
to get the brain nudged in a different direction. . .

And I thank Joel again for his nudging!