Userform overwriting previous entries

[julhs]

This may be classed as a cross thread, as original ended up in ‘General’ due to oversite, but still not resolved things.
Other thread http://www.excelforum.com/showthread...t=#post4575862

Have one aspect of the UserForm to resolve as there is still a flaw that need to code for or Error handle.

Data is entered where supposed to; it ignores blanks above last entry and puts data in next empty, using R as reference range for next blank row, looking up from R22.

Problem comes when range R9:R22 is full (may be with blank rows in it). If another entry is then made that entry will then overwrite an entry already there, where that might be entirely depends on whether there is any blank rows, if no blanks it will overwrite R10

I need to prevent this from happening.
Simplest being msg box telling you R22 is not blank so go and insert more rows.
Ideal solution would be to use a NamedRange from the Totals Row (i.e., T24 (Cash Paid), this book is only a demo so only few rows, actual one will have 500 – 1000, T24 (Cash Paid) will be anywhere within range T500:T1000.

My trouble is; I think one of the above would solve it but I don’t have the knowledge to implement it, can anyone help?

[JapanDave]

What is the code that is giving you issues? I am too tired to look through all that.

Cheers

[julhs]

Hi JapanDave,

This is the part that I am referring to:

Please Login or Register  to view this content.

Using this just to test for blanks but it is returning R9, when R9 clearly contains data,
so something odd going on

Please Login or Register  to view this content.

[julhs]

Been trying various things but still not getting the right outcome. Regarding this part, have wondered what the significance of using [R9] as opposed to (R9) in the snippet below.

Please Login or Register  to view this content.

[jaslake]

Hi julhs

Try this for finding the last row in Column R...

Please Login or Register  to view this content.

[julhs]

Hi Jaslake

Thanks, it works perfectly as I would have expected. BUT!!

In the actual Accounts Book there will be Summary data in the range O24:R100 so there is effectively a bottom line to the input section and I don’t want data being inadvertently added into it. So it’s how to work around that issue that trying to deal with. (beat myself up more like)

Don’t know if you’ve had a look at the attached Dummy book but the Summary section O24:R100 is actually missing, but you will see the effect of what happens when the next entry is made (into what is effectively a full column) because of the coded I've put in place to take account of the Summary Section.

It’s very hard to explain what is happening, but it’s very clear if you made one more entry (may be 2)

[jaslake]

Hi julhs

It would be helpful if your Sample File has the same structure as your actual file.

actual Accounts Book there will be Summary data in the range O24:R100

[julhs]

Hope this helps

[jaslake]

Hi julhs

Forum is squirrely...just going to upload the File..let me know...

Please note the Formula change in your Total Lines...

Please Login or Register  to view this content.

[julhs]

Hi jaslake

Brilliant, you are an absolute star; you’ve sorted my conundrum and improved things massively

I’m very grateful

julhs

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.