Macro / Msgbox VBA help!

Hi there,
I have an issue come up with one of the macro in my spreadsheet,

I would like it to search through the dates in column Y (the first date starts at row 20), and if every single one is larger than LDays (which is set to 31), to display a message box (but only once - currently it displays it for every single date that is larger than LDays.) - so basically if all the dates are more than 31 days in the future.

I have tried searching online and trying different things, but can't seem to solve it.

I think it may have something to do with the specific IF statement lying within the

Please Login or Register  to view this content.

part of the code. I have tried it outside of this part, but then the message box constantly pops up.

Everything else works fine, other than this part:

Please Login or Register  to view this content.

This is the complete macro code so far:

Please Login or Register  to view this content.

Many thanks!

put a global flag to show/not show the message anymore

public gbHideMsg as boolean


then once it shows, turn it on and it never shows again:

Please Login or Register  to view this content.

This works properly when there are no expired or any within 31 days, but even if there are any that are expired or within 31 days it still pops up.

Any idea how to solve that??
Have I put the bit of code in the wrong place? I have put the old statement in as a comment for my own use.

Please Login or Register  to view this content.

Can you upload a sample sheet? Where added code fires when it shouldn't?
When I tested on my end, it behaved as expected.

Spreadsheet attached

Ah, so your cell has some formats etc and rCount comes back as 56. That's what's throwing off calc. Use Column "Y" instead of "Z" for rCount.

Also, your insert location is off. The check should happen before While LRow < 200 line (completely outside of loop).

Following should work.

Please Login or Register  to view this content.

Huh? I'm not sure I get you. It only shows msg box "There are either no expired safeguarding certificates, or no certificate expiring within the next 31 days."...
When there are no item that falls within 31 days or less. I.E. When all expiry date is over 31 days away.

Works fine on my end... see image below.
0.JPG

I've tried using those dates, plus the British way of those dates, and I am still not getting the msgbox.
Are you able to attach the spreadsheet back to me to see what seems to be different between mine and yours?

Here you go.

I have downloaded the spreadsheet and tried using the button, but nothing happens.

You used the file I uploaded right? Works fine on my end. Did you forget to enable macros?

If you click as is, it will show the message for no certificate expiring within next 31 days.

Macros all enabled. I click the button, and nothing happens.

No idea why it is working on yours and not mine.

It's not likely the code itself. Try checking your Personal.xlsb and if you have add-ins turn them off and see if any is interfering with the code.

Other than that, I'm can't say what may be causing this. It's fairly simple and straight forward code.

I think I have managed to sort it with:

Please Login or Register  to view this content.

Try that and see if that works on yours.

Hm? Just use it if it works for you.

Does that not work on yours?

If you try it so there is an expired date, expiring date, and one over 31 days away, it shouldn't appear - or at least it doesn't on mine.

Looking at code I see no reason it should not.

I just prefer not to check something in every iteration of the loop when I can check in one shot outside of loop

Okay forget that, I have tested it several times in different scenarios, and it appears that if you have two dates that are over 31 days it appears - even if there are some that have expired.

So pasting code from post #7 doesn't work? Is there formula in Column Y?

No since it was in Y, I have added another column for another detail.

It seems to now work if I use AND NOT when listing the formulae previously used.

Please Login or Register  to view this content.

And that now doesn't work if all the dates in column Z are more than 31 days.

I just don't know what alternatives or other ways there are now.

If your sample accurately represent your actual workbook, my code should work. Tested on Excel 2013 & 2016. On Win 7 & 10.

Here's alternate approach using different logic. See if this at least gives you correct result.

Please Login or Register  to view this content.

This is the result I cam currently getting - the first name and surname of the person are not appearing. The wording for those that have already expired, and those that are expiring should be slightly different. Those that have expired should be like it was before with the wording and then just the date. vba.jpg

Then your workbook structure changed from sample.
Upload sample that accurately reflect your actual structure.

I'm using the same one as you sent me back.

From what I have just tested (with my old code), I think I have sussed out what the problem is.

The 'no expired' message is determined by what the last date in the column is.
* the last date in column Z is expired, it doesn't come up - how it should be.
* the last date in column Z is not expired or is not expiring, and there are no expired elsewhere, it comes up - how it should be
* the last date in column Z is not expired or is not expiring, and there ARE expired dates somewhere else, then the message comes up - should not be like this

It must be down to one of these two bits

Please Login or Register  to view this content.

There must be one part of it that it is making it execute by determining whatever the value in the last cell is?

My code doesn't use cells/range other than to put it into array. tempArr(i, 26) is equivalent of checking column Z for the date in array.

Well, no wonder you don't get first and last names for items that expired, or is going to expire within 31 days... you don't have it in your data.

Only row 20 to 22, which all are more than 31 days away have names in column A & B. Fill First & Last appropriately and you will see correct result.

FYI - Anything above "====" separator is items that's going to expire within 31 days, anything below is those that already expired.

Sorry, my testing was on my old code.

I completely forgot there weren't names in the cells for your code!
That seems to work perfectly now.
Thank you!

Also, is there any way of making it so the length of the names don't matter? Currently there are massive gaps between names and the rest of the text as the length of the string is predetermined in the code.
I would like it so there is just one space between the names and text, rather than having the predetermined space (I hope that makes sense).

This is brilliant, thanks for all your help!

I have one more request:

So I also need the array to identify where there might the expiry date may have a gap (as long as there is a name in column A and B).
I have tried using the following code in different parts of the vba, but cannot get the array to display it correctly - currently the array displays it among the 'Expiring' section'.

Please Login or Register  to view this content.

Not sure I understand your requirement. Can you demonstrate it in a sample workbook?

So what I need is:

If there is no date found in a cell in "Expiry Date", but there is a name (or characters) located in First Name and Surname (so column A and B), then a message will appear - the message similar to that listed in the code above.

Does that make sense?

This has no effect on anything - nothing happens!

Sure it does. Did you add data with just the first and last name without the expiration date?

Yep!
I no longer even get the array for if one has expired / is expiring.

Upload your sample that this code produces nothing on.

Works fine on my end.
0.JPG

It seems to work okay on this dummy copy, but on my main spreadsheet (which I can't show), it doesn't work. Is the array currently limited to a certain number of rows?
My main spreadsheet is down to row 117, and will continue to go down the page further.

No, array will expand based on what is in Column A, and as far as I know there isn't set limit (I routinely work with array with row size over 400k).

And will span from "A20:Z" & last row with data in column A. Ex. A20:Z28 in your sample file.

Likely culprit is some structure difference from sample to your actual workbook.

All the columns in my main workbook are the same as the one in the sample one I sent, so absolutely no idea as to why it isn't working.
Tomorrow I will send over my main workbook (but I will remove the confidential data), so you can see exactly how that workbook is set up.

I will try and workout why it isn't working in the mean time.

Sorry it has been so long.

Please see the attached spreadsheet.

The array doesn't seem to come back with anything.

Thanks!

Brilliant, thank you!