Hi Paul

Please check the attachment. Hope it helps.

Clark

Thanks, Clark, that works really well. I found that if I converted B8:B15 to a table and inserted the table name in the code to replace B8:B15, it would work. I inserted a few columns to expand the table so that it resembles the table I'm using in my file. Interestingly, the code will then check the entire table to see if there's anything in there.

The table I'm using in the actual file for which I'm adapting your good work is a database. There are entries by month, looking like the table in the attached file. What I need to add to the code you've created is a way to distinguish whether there is data in the table for specific months that correspond to to the subs for Month1()_Click, Month2()_Click, and so on. In the sample file, instead of checking for anything in the entire table, it would check MonthYear only for Month1 (January).

Here's the attachment. Thanks

So, any ideas?

the first code line:

With Range("F8:F15")

Use a fixed range F8:F15, not a table range, the table will automatically expand.

Okay, that helps refine it. Now the code looks only for something in the first column. But if I want to check by date in Table 2, how do I do that? How do I specify to perform the operation only if Month1 = 12017?

Hi Paul

From what I can see, you only wanted to extract data from a table to another range based on some criteria(s). Am I correct?

If this is what you want to do then have you tried using Advanced Filter? It is easier to use and require very little VBA code. You can also do that without any VBA at all. It is flexible and easy to maintain than using VBA.

You can see an example in my attachment. You can actually ignore my VBA (extract button) and extract it manually using Advance Filter if you want to.

1) Blue - Data

2) Red - Criteria

3) Green - Output

If for some reason you require VBA, then selecting via checkboxes may not be correct unless you wanted to select multiple months. There may be other better options.

Unless we know exactly what you want to do, suggesting a correct solution for you will only be guessing work as best.

Hope this helps.

Sunny

Sunny,

What I'm actually doing is executing a few commands when a checkbox is selected. I have twelve checkboxes, one for each month of the year in my model. If TRUE, then copy and paste values into another area of the worksheet. If FALSE, display a message that there's no data and end the operation. To know if there's no data, I want the code to check a separate database (table format) that contains entries for particular months. So I do need code.

Since this post, what I've done is to add another criterion in Excel in addition to the TRUE cell in B5. This new cell is a SUMIF for particular months in the database. For example, if SUMIF returns 0 for 12017, I know that nothing's in the database for January 2017. The code could then add a condition: If B5 is TRUE and B6 is greater than 0, proceed with the sub, otherwise go to the message and end it.

For this purpose, you could just put a 0 or a 1 in B6 to test it. I can adapt the code to my needs once I see the syntax.

Does that help?

Thank you

Hi Paul

Thanks for your clarification. It does help.

Please refer attachment.

The code filters the table with the required criteria and check if any record is displayed using the SUBTOTAL to count the visible records.

It will display a message if no records are found (visible).

Hope this helps.

Sunny

This is too difficult to adapt for me. I wonder if I can give you the code and I have and then prevail upon you to add the lines I need to make it work the way I want with an additional condition.

The actual workbook is 20MB in binary format, so I'm attaching just the worksheet tab under consideration (ignore the links). Here's the code that needs a few extra lines:

Sub Month1_Click()
If Range("TrueMonth1") Then
Application.Calculation = xlCalculationManual
Range("CMIMonth1paste").Value = Range("CMIMonth1copy").Value
Range("MDCMixMonth1paste").Value = Range("MDCMixMonth1copy").Value
Range("APCMixMonth1paste").Value = Range("APCMixMonth1copy").Value
Range("IPratesMonth1paste").Value = Range("IPratesMonth1copy").Value
Range("OPratesMonth1paste").Value = Range("OPratesMonth1copy").Value
Range("IPMixMonth1Part1paste").Value = Range("IPMixMonth1Part1copy").Value
Range("IPMixMonth1Part2paste").Value = Range("IPMixMonth1Part2copy").Value
Range("OPMixMonth1Part1paste").Value = Range("OPMixMonth1Part1copy").Value
Range("OPMixMonth1Part2paste").Value = Range("OPMixMonth1Part2copy").Value
Range("DischChangeMonth1paste").Value = Range("DischChangeMonth1copy").Value
Range("VisitsChangeMonth1paste").Value = Range("VisitsChangeMonth1copy").Value
Range("LOSChangeMonth1paste").Value = Range("LOSChangeMonth1copy").Value
Range("IncStmtMonth1paste").Value = Range("IncStmtMonth1copy").Value
Range("BalSheetMonth1paste").Value = Range("BalSheetMonth1copy").Value
Range("CapSpendMonth1paste").Value = Range("CapSpendMonth1copy").Value
Range("IncrDecrMonth1paste").Value = Range("IncrDecrMonth1copy").Value
End If
Range("ProductivityMonth1paste").Value = Range("ProductivityMonth1copy").Value
Application.Calculation = xlCalculationAutomatic
End Sub

Month1 is the code above, using "assigned macro" for the checkbox right under cell T19. I've used range names as copy/paste references in case I insert rows or columns later. The checkbox link is AG19. In cell AG20, I have a formula that sums everything in HistoricalActualsDB table housed in another tab (not shown) for Month1. I have 12 of these subs, one for each month of the year. The subs copy formula calculations and paste their values into the appropriate places. The destinations are future months on the assumptions tab (not shown). Users enter their assumptions into these cells and it drives the forecast model's future months. The user specifies which months are actuals and which are forecast so the model knows what to do. If it's an actual (historical) month, then actual data overrides the forecast that would otherwise be calculated from the assumptions entered (using IF statements). The assumptions entered for actual months will then have no effect. As each month is closed out, actuals should replace the assumptions that were entered, but you can't have both data entry and formulas at the same time in the same cell. The code nicely solves that.

To give an example: say you think salaries will go up 5% in February. You enter 5% in the appropriate assumptions cell, and the forecast increases salaries by 5% in February. Then February comes around and the books are closed. February's actuals then replace whatever you forecasted for that month. Let's say the salary increase got delayed until April. Yet in the assumptions cell for February still sits the 5% you entered, which has no effect now. It should say 0%, which is the actual result. And if you're using history as a guide for what to enter in your forecast, you want to see 0%, not 5%. So, the code updates this for actual results.

So far, the code works really well. But when it comes to a future month with no data loaded in the database, it'll start copying and pasting zeroes and causing problems. So my intent is that the code will check for both TRUE and AG20>0. Note that, in a year from now, when I advance the model's current fiscal year dates to 2018 and 2019 and so on, AG20 will find the first month of the fiscal year, which can vary. If the user selects a month for which there isn't yet data, a warning message then comes up and the sub ends. My issue is that the first condition, If Range("TrueMonth1") Then, needs to add AG20>0. I don't know how to do this in VBA.

Paul

Hi Paul

Is there suppose to be an attachment?

From your statement

My issue is that the first condition, If Range("TrueMonth1") Then, needs to add AG20>0. I don't know how to do this in VBA.

Are you looking for this?

If Range("TrueMonth1")  And AG20 > 0 Then

   <your codes here if everything is ok>

Else

    Msgbox ("There's nothing in there. Click to cancel."), vbCritical

Endif

It will check the range TrueMonth1 to see if it is TRUE (i.e. checkbox is selected) and also cell AG20 to make sure that the value is more than 0 (i.e. data is available) otherwise it will display an error message.

Sunny

That's it! I changed AG20, above, to Range("AG20"). The syntax is different from Excel. I don't know why there are spaces between characters that would not be allowed in Excel, for example, AG20 > 0 instead of AG20>0, but I followed your example to ensure it works.

You've helped me before on this forum, and many others, too. I wish there was some way that I could return the favor.

Thanks, Paul

Hi Paul

My bad. I left out the Range("AG2"). 

I think I got overly excited when I saw your last statement and understood what you actually wanted and totally mixed up my VBA and formulas in the excitement.

You can contribute by helping others in this forum. The more you help the more you learn (that is my MOTTO).

You can also spread the word about MOTH to others. (I don't get paid for saying this , I am just an ordinary member at MOTH)

Cheers

Sunny