What's wrong with Range in this For loop

[pumper]

It says Compiler error
Wrong number of Arguments or invalid Property Assignments

Please Login or Register  to view this content.

]

[mehmetcik]

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[mjr veverka]

3rd variant

Please Login or Register  to view this content.

[pumper]

porucha vevrku

Your suggestion worked but I was hoping to keep the for loop involved so I don't know how to add the i in the range

Fluff
Your suggestion seemed to get lost in the for loop.

[Fluff13]

Not quite sure what you mean when you say it got lost in the loop.

[pumper]

fluff13
I had to shut excel down to get out of the Macro
I don't know if it was the loop or not just seemed like it. Maybe the i got changed from integer to string so the for did not like it.
If I add a Stop before the Next i it works for 1 loop

mehmetcik, your suggestion does the same thing

[Fluff13]

What is happening when the data is copied to BN3?

[pumper]

fluff13
On the first loop the data gets there correctly

[Fluff13]

Do you have any event code, that is triggered by copying the data?
You should not end up in a permanent loop with the code you posted.

[Sintek]

On the first loop the data gets there correctly

You are overwriting the data all the time...Each loop is pasting to same cell BN3...?

[mjr veverka]

It seems that the copied cell values (to the 'BN3:BW3' range) ​​from rows of individual columns (i.e.: L.row, Q.row, V.row, AA.row, AF.row, AK.row, AP.row, AU.row, AZ.row, BE.row) are used to create calculations "on the fly" for other purposes/calculations ... (???)

[pumper]

fluff13
To clarify my last reply
If I have a Stop before Next i the data for the first loop is correct

If I don't have a Stop then if it runs it goes quickly to about i=12 then it takes about 3 seconds for each i in the loop and BN3 shows the correct value.
Makes me think it is running out of memory.

[Fluff13]

I understand that, but why are you copying it to BN3 every time?

[mjr veverka]

it takes about 3 seconds for each i in the loop and BN3 shows the correct value

Attach some reliable/trustworthy 'xls' file example (data must be false => data privacy and confidentiality protection).

[pumper]

When I get it to work, which it may be now if memory is a problem, there will be other code in between so I will put it in my big program to test.
When all the data gets to BN3 etc I will find the Min value which I can't do when the data is not contiguous.
That will happen in between each "for i" so overwriting data should not be a problem.

[pumper]

porucha vevrku
Yes I am putting it there so I can find the Min value which I can't do when the data is not in contiguous cells.

[mjr veverka]

Min value from the whole "non-contiguous" range, i.e. ranges: L5:L33, Q5:Q33, V5:V33, AA5:AA33, AF5:AF33, AK5:AK33, AP5:AP33, AU5:AU33, AZ5:AZ33, BE5:BE33 (at once) ?
Or min value for any individual row of "non-contiguous" range (L & row, Q & row, V & row, AA & row, AF & row, AK & row, AP & row, AU & row, AZ & row, BE & row), one by one (29 times) ?

[Fluff13]

If you just want the min value from each row, you can use

Please Login or Register  to view this content.

[Rick Rothstein]

If you just want the min value from each row, you can use

Please Login or Register  to view this content.

You can write the highlighted line of code in a more compact and (slightly) less ugly way (the "i" variable only appears once and there are no concatenations) like this...

Please Login or Register  to view this content.

Even more compact and still (slightly) less ugly (again, the "i" variable only appears once and there are still no concatenations) although not as easy to read which columns are involved because you can't see the letter designations)...

Please Login or Register  to view this content.

[mjr veverka]

Variant 1:

Please Login or Register  to view this content.

Variant 2:

Please Login or Register  to view this content.

Min value for Variant 1 <> Min value for Variant 2.

[pumper]

I can't thank all of you enough.
I don't know if I will ever learn as much as all of you know but I will keep trying.