Problem with macro, gives error

[abjac]

Hi I have below macro, which i have been fighting with some time now, so would be good if some can help me.

The macro copy fields from shee No Pagado to sheet Masterinvoice.
I have problemmake it copy cell AK37 in Sheet No Pagado, to cell K37 in sheet Masterinvoice
I think the problem can be in this string, but i am not sure. If i type AF instead its working fine, but dont then copy this cell.

This is the string i think can have the problem.

Please Login or Register  to view this content.

Another thing is that in the code i unprotect the sheet and protect it again. But when i protect it again, i need it to put in the option
Select unlocked cells, like this is ticked if you do it manually, all other are unticked.

Here is the code and also there is a sheet you can have a look at.

P.s code for protect sheet Masterinvoice is 1111

Thanks

Abjac

the problem code.

Please Login or Register  to view this content.

[TMS]

For the password protection, you need:

Please Login or Register  to view this content.

[abjac]

Thanks TMshucks great..I guess it have to be put in here infront of the one i had before.

Thanks Allot

One more to go the other give me problems

Please Login or Register  to view this content.

[TMS]

You'd set it up something like this:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

I should have mentioned that the variable sPW is a global string variable that I use to store the sheet password.


Regards, TMS

[abjac]

Thanks TM. Its great to have this overview, also for later use. Thanks allot.. Hope for the other also. That really have been giving me headacke haha. But thanks for this

Sincerely

Abjac

[TMS]

Please Login or Register  to view this content.

Note that you have a comma (,) after the value "K37". Hence, when you split the string using comma as a delimiter, you get an empty value for the last element. That is then used in:

Please Login or Register  to view this content.

and that means you have an invalid range address ... sarr(j) will be blank.

However, if you fix that, you have 33 elements in sarr, 0 to 32. But column AK is column 37, so you are processing columns from A to AK ... 1 to 37, hence you get a "subscript out of range".

You need to add some addresses to the range string.

Regards, TMS

[abjac]

Hi TM And thanks, Yes i know there is something wrong. If i fix the first i still battle with the other. I have tried allot, but so far no luck. So any help from some who are a little better to VBA than me will really be great.

But thanks TM so far, really great

Sincerely

Abjac

[TMS]

In all honesty, there is little further that I can do to help you.

Basically, you have 37 source addresses in columns A to AK and you are copying them to the target addresses in the string array variable, sarr, which has only 33 elements, 0 to 32. The implication is that you need to add 4 target addresses to the sarr array.

Only you know where the source data needs to go; hence only you can add the appropriate target addresses into the array.

If you step through the code, you can watch where you are putting the data and confirm that it is going where you expect it to.


Regards, TMS

[abjac]

Hi TM. I think i get it now and know also whats wrong. The problem for me is that those 4 target addresses i dont want, because there is formulaes in these cells.
I have been thinking and as i see it there is only 2 ways 2 handle it.
Either i move the column so the info en parte pagado are in column AG and change range to AG. It will work.

Or another workaround could be that i keep the column there, and then i "dump" the 4 missing target addressed, in cells long away from the form in Sheet "Masterinvoice" example cell AV8 to AV11.

That will work and it will not do anything, and i can keep my column as it is now.

I think i will do the second option, it seems to be fine.

Thanks allot TM for helping me understand this, i have been strugled with this quite some time. So great you took you time to explaing.

Will mark this as solved now, dont think there will be more and better solution.

Sincerely

Abjac

[TMS]

There is a third option ... isn't there always at least one more than you thought?

The original problem was because of the extra entry at the end of the list (generated by the extra comma). However, we can take advantage of that by actually adding more blank entries and then choosing to ignore them in the loop.

In the example below, I've just added the blank entries at the end of the list but, realistically, you could put them in anywhere.

Please Login or Register  to view this content.

I really like this way of populating non-contiguous cells on a form ... I'll bear it in mind for when I have something similar to do.

Kind regards, TMS

[abjac]

Hi. TM. yes off course i didnt think of that. in d bus on my android. will try this later. its a great 3 way.
Thanks allot.
Sincerely
Abjac

[TMS]

You're welcome.

[abjac]

Tried it home now . works like a charm thanks allot TMS for all your help

SIncerely
Abjac

[TMS]

Agai, you're welcome. Glad we found a solution.