How Do I Expand A Set Range In The Match Command

[Minitman]

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

 

[JE McGimpsey]

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)

 

[Minitman]

Thank you very much!

That does indeed work well! :^)

-Minitman