PDA

View Full Version : If statement to copy data to correct row



Carla1973
08-10-2018, 03:21 AM
Hi,
Please help. I have a headache from setting up these macros now.
I currently have the Submit macro to run so that it pastes the information from the purchase requisition sheet into the next available row on the log sheet. However, this won't work if any data isn't completed sooooo my question is two fold.

1) Is it possible to ask VBA to check all mandatory fields are entered on the purchase requisition sheet and bring up a message to warn the user and not run the macro if they are not?
2) How do I change the code so that, instead of pasting to the next available row, the purchase requisition number in cell G2 does use this method, but then all other columns in the log sheet are populated by looking up cell G2 from the A column in the log sheet and returning the relevant information to the correct column in the log sheet from the purchase requisition sheet? I then also need to apply the same to the approve macro so that it pastes to the correct row in the log as they may not be approved in order.

I hope this makes sense. I have attached a sheet with some sample data in so that you can see what I mean.
The password to unprotect sheets is Twins0105.

I will be eternally grateful :-)

Silent
08-11-2018, 09:28 AM
Hi Carla,

1. yes vba can this.
2. i don't understand all off your process? ok first user fill up the list and than copy to the log with the Requisition Number ok. And after this you delete the list and set the Requisition Number +1 and how you will aprove this in the same file or what?

look and test what i done

Thx & Cheers
Silent

Carla1973
08-13-2018, 12:47 AM
Hi,

Thank you so much for looking at this.
The log looks good as it seems to be posting to the correct lines.
However, I need it to force the submitter to email before the next steps are taken.

Also, when I open the emailed version, I am unable to enter data into the approval fields 'Approved By' and 'Approved Date'?
Thank ypu again,
Carla

Silent
08-13-2018, 09:28 AM
Hi,
you unprotect your sheet
than you click on the locked cell with right click and go to "Protection" or whatever in your english excel version is the last tab right side.
than you make the hook out at locked and than you can protect your sheet again and than done

all works now fine?
thx and cheers from Germany^^
Silent

Carla1973
08-15-2018, 04:16 AM
That's great!!
Thank you for all of your help. Just one more request though.
If the data is missing and the message box pops up, how do I stop the macro from running so that the user can enter the missing data before they submit the form again please?

Silent
08-15-2018, 08:48 AM
Hi,
but the marco don't make anything if some row is red and some data missing? Clear the log write some lines and let some empty and than click on submit and the error message appears and than you see that the marco don't make anything in the log? Or what did you mean?