Trying to get worksheefunction match to work
 

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 a
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!

Trying to get worksheefunction match to work
 

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" wrote:

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 a
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!

Trying to get worksheefunction match to work
 

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.





"Joel" wrote:

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" wrote:

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 a
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!

Trying to get worksheefunction match to work
 

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" wrote:

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.





"Joel" wrote:

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" wrote:

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 a
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!

Trying to get worksheefunction match to work
 

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!




"NigelVII" wrote:

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" wrote:

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.





"Joel" wrote:

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" wrote:

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 a
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!