How Do I Expand A Set Range In The Match Command
 

Greetings,

I have a problem with the number of rows my formula will look at.

The code uses MATCH to find the row of the invoice being modified. As
you can see from the code sample it is limited to 1765 rows.

Dim x As Long
Dim wks_target As Worksheet
Dim wks_source As Worksheet

Set wks_target = Worksheets("INV")
Set wks_source = Worksheets("Enter")

'get row_number
x = Application.WorksheetFunction. _
Match(wks_source.Range("AP2"), _
wks_target.Range("A1:A1765"), 0) '<<<
'paste values
wks_target.Rows(x) = wks_source.Rows(100).Value


How can this be changed to accept an unlimited number (within the
65536 Excel limit). I think that

Range("A65536").End(xlUp)

is what I need, but I don't know how to insert it!

Any help would be appreciated,

TIA

-Minitman

How Do I Expand A Set Range In The Match Command
 

One way:

x = Application.WorksheetFunction. _
Match(wks_source.Range("AP2"), _
wks_target.Range("A1:A" & wks_target.Range("A" & _
Rows.Count).End(xlUp).Row), 0)

In article ,
Minitman wrote:

Greetings,

I have a problem with the number of rows my formula will look at.

The code uses MATCH to find the row of the invoice being modified. As
you can see from the code sample it is limited to 1765 rows.

Dim x As Long
Dim wks_target As Worksheet
Dim wks_source As Worksheet

Set wks_target = Worksheets("INV")
Set wks_source = Worksheets("Enter")

'get row_number
x = Application.WorksheetFunction. _
Match(wks_source.Range("AP2"), _
wks_target.Range("A1:A1765"), 0) '<<<
'paste values
wks_target.Rows(x) = wks_source.Rows(100).Value


How can this be changed to accept an unlimited number (within the
65536 Excel limit). I think that

Range("A65536").End(xlUp)

is what I need, but I don't know how to insert it!

Any help would be appreciated,

TIA

-Minitman

How Do I Expand A Set Range In The Match Command
 

Thank you very much!

That does indeed work well! :^)

-Minitman


On Sat, 24 Apr 2004 21:20:33 -0600, JE McGimpsey
wrote:

One way:

x = Application.WorksheetFunction. _
Match(wks_source.Range("AP2"), _
wks_target.Range("A1:A" & wks_target.Range("A" & _
Rows.Count).End(xlUp).Row), 0)

In article ,
Minitman wrote:

Greetings,

I have a problem with the number of rows my formula will look at.

The code uses MATCH to find the row of the invoice being modified. As
you can see from the code sample it is limited to 1765 rows.

Dim x As Long
Dim wks_target As Worksheet
Dim wks_source As Worksheet

Set wks_target = Worksheets("INV")
Set wks_source = Worksheets("Enter")

'get row_number
x = Application.WorksheetFunction. _
Match(wks_source.Range("AP2"), _
wks_target.Range("A1:A1765"), 0) '<<<
'paste values
wks_target.Rows(x) = wks_source.Rows(100).Value


How can this be changed to accept an unlimited number (within the
65536 Excel limit). I think that

Range("A65536").End(xlUp)

is what I need, but I don't know how to insert it!

Any help would be appreciated,

TIA

-Minitman