Macro to say message if certain criteria are met

[sandbach]

Hello
I was hoping somebody could help, i have received lots of help already with my macros for which i am very grateful and have learnt a small bit, unfortunately not enough!. i have had a good go at doing this one myself but find myself at a loss.

What my macro is trying to achieve is to save a range of data if certain criteria are met.

The sheet in which the data is held is "sheet3".

If column S contains the words "resolve wsa" or "resolve list"

and column U contains the word "open" or column V contains todays date ( not actually 27/10/10 but the data when this is accessed),

then i wish my macro to then pop up a message box saying, "save your worksheet"
but if this criteria is not met then i want it to pop a message " saying no need to save"


Im sure this is quite possible, but im just getting no where with it so any help would be really appreciated.


Kind regards

Joe

[davegugg]

Do you want those words to be present anywhere in the column, or just in a certain row?
What do you want to trigger your macro?

[sandbach]

Sorry, knew i hadnt been clear enough!

I intend to add the code on to a button with code on it already.


sorry to be clearer on the code if in the same row, any row, of a range of preselected data, i want the macro to read it and if in column s contains the words "resolve wsa" or "resolve list"

and then to read column U and if that contains the word "open" or if column V contains the date on which the data is accessed

then if these criteria are met, i want it to pop if with a message "you need to save your worklist" but if these criteria are not met, then i want a message to pop up saying" you do not need to save a worklist"


I have had a good goo at this myself but getting no where and it contains sensitive data so i cannot send the workbook,. but any help really would be appreciated

[davegugg]

The only thing you've still left out is which rows you want to search. For the purpose of my code below, I've assumed rows 3 - 5. However, to change this just change where lngTop and lnbBottom are defined.

Please Login or Register  to view this content.

[sandbach]

Thanks very much Dave,

ill plug that into my workbbok when i get to it tomorrow, hopefully it will do the trick, thanks very much.

[sandbach]

Hello Dave,

Right i have tried the code but it doesnt seem to be working i have amended the code but may have messed it up.

the code i have put in after the first one didnt work, which i think may have been to my lack of info!
anyway what i have added is

and it is to correspond with a mock up of the type of data on the attached file, with which rows 7 and 15 meet the criteria to flash up the message "save it" all other rows should flash up the other message as they do not meet the criteria.

I hope this provides enough information.

Thanks for ur efforts already.


Joe

[davegugg]

I'm very sorry, I miss-read an "or" with the date and open/closed in your initial request.

I ran your code in your example and it worked for me. I'm not sure what text editor you are using, but my vba didn't interpret your quote marks correctly. After fixing them and changing the reference to sheet 1 (instead of sheet 4) it told me to save after row 7 and row 15.

[sandbach]

Ha, i knew i hadnt written it the most understandable way!

When the data, is returned in that form, when it scans the whole document, it was working correctly with the first IF

but through trial and error the second IF didnt seem to be working, so it was returning the wrong end instructions.

i may not have explained this properly, but i need it to check the whole sheet and if any of the rows meet the criteria to then show the save it message

can u advise if there are any errors in it?

[davegugg]

I'm not sure what else to tell you. I've modified the macro to run in your example, check the workbook I've uploaded. Hopefully that will help.

[sandbach]

Thanks Dave very much for your efforts.

I have tested the uploaded workbook with the code in, it seems to be working perfectly except if i have the criteria (a) resolve isa (d) closed and (f) 29.10.10 - yesterdays date

it returns u should save ur worklist, but this is incorrect, if the date was 30.10.10 - todays date, it should return "u should save ur worklist" but not if its yesterdays?

Can u advise in anyway?


Kind regards

Joe

[sandbach]

ha the date was in wrong format!!!!!


works perfectly now, thank u very much!!