Macro works perfect on xls.files but not on xlsx.files

[FrankVH]

Dear All,

Run time error 1004 when using sheet with more than 65000 lines .xlsx ). Even if they are not used.
I fear it has to do with the way I search the last used row.

Endrowhelp = Firstref.Column


Cells(50000, Endrowhelp).Select
Selection.End(xlUp).Select
myEndRow = Selection.Row
.......

My macro works perfectly when i use .xls files (the all have max 65363 rows)

Help

 

[FrankVH]

Dear Fluff,

Run time error 1004 Application defined or object defined error

ActiveCell.FormulaR1C1 = _
        "=INDEX('[" & g & "]Sheet1'!R1:R" & mijnBronEindRij & ",MATCH(RC" & mijnDoelEindrijHULP & ",'[" & g & "]Sheet1'!C" & h & ",0),MATCH(R" & mijnDoelStartrij - 1 & "C,'[" & g & "]Sheet1'!R1C1:R1C255,0))"

 

[Fluff]

what are the values of your variables when you get the error, as that works for me

 

[FrankVH]

when using a file "g" with >1.000.000 rows and > 250 colums --- error with folowing values
mijnBronEindRij = 122
mijnDoelEindrijHULP = 9
h=1
mijnDoelStartrij = 2
when copy/paste the complete data of the "g" file in a "g" file with only 65236 rows and 256 colums ------ working perfectly breakpoint after problem code line gives same values
mijnBronEindRij = 122
mijnDoelEindrijHULP = 9
h=1
mijnDoelStartrij = 2



Thx

Frank

 

[Fluff]

Ok, is this in an xls file?
If so you will need to limit the full row & col references

ActiveCell.FormulaR1C1 = _
        "=INDEX('[" & g & "]Sheet1'![COLOR=#0000ff]R1:R" & mijnBronEindRij[/COLOR] & ",MATCH(RC" & mijnDoelEindrijHULP & ",'[" & g & "][COLOR=#0000ff]Sheet1'!C" & h & "[/COLOR],0),MATCH(R" & mijnDoelStartrij - 1 & "C,'[" & g & "]Sheet1'!R1C1:R1C255,0))"

 

[FrankVH]

How do we do this ?

 

[FrankVH]

I tried

ActiveCell.FormulaR1C1 = _        "=INDEX('[" & g & "]Sheet1'![COLOR=#0000ff]R1C1:R" & mijnBronEindRij & "C99[/COLOR],MATCH(RC" & mijnDoelEindrijHULP & ",'[" & g & "]Sheet1'![COLOR=#0000ff]R1C" & h & ":R210000C" & h & [/COLOR]":,0),MATCH(R" & mijnDoelStartrij - 1 & "C,'[" & g & "]Sheet1'!R1C1:R1C255,0))"

 

[Fluff]

If the code is in an xls file, I think that you can only refer to ranges of A1:IV65536.
If the data you want is outside of that range, you will need to convert to an xlsm file.

 

[FrankVH]

Dear Fluff,

Thx I replaced 210000 by 64000 in problem code line and it does the job.

Many thanks

There should be a solution if number of rows in source file >65300, not ?

 

[Fluff]

Glad you sorted it & thanks for the feedback.
If your data in the source file extends below row 65536, then I don't think you can use a formula to get the info.