Print the list of filenames without submission

[chaijing]

Hi all,

I'm very new to VBA and am trying out the coding on my own. I have a list of budget files stored in a folder, and I would like open each of them and refer to a specific worksheet name to check if there is any submission.

I need to code such that if there is no entry in the table (i.e. none filled at all from columns C - H and rows 3 - 25), the macro should call out the filename and list them in the macro-run workbook and worksheet. The macro will then loop until it has gone through all the budget files stored in the folder. I am not sure how many budget files there would be in total.

File directory where all the budget files are stored: D:\05. Budget\FY17\Trial\
Worksheet name where departments will submit request = M.Manpower
File directory where the macro will be run from: D:\05. Budget\FY17\Budget Consol.xlsm
Worksheet name for the list of filenames to be called out = None

I have also attached a sample workbook on how the budget file will look like.

Appreciate any help / advice on this. Thanks so much!

[dflak]

Will the real budget file have formulas in the range(C3:H25) like the sample does?

[chaijing]

Hi dflak, some of the columns would have. Majority would eventually become dropdown list though.

[dflak]

OK, I'll have to do some research. I was going to use COUNTA to determine if any of the cells were filled in. I can't do that since COUNTA (Range) > 0 if the range has formulas even though the cells appear to be blank.

[chaijing]

Thanks dflak! I've attached another sample which is closer to what I will use eventually.. Basically Column C will have the cost centre appear should the user make any selection from the dropdown menus / input any value from Columns D to G, I to L, N to T. (Column G will trigger a vlookup value in Column H),

In fact, if nothing is entered within the row (Columns D to G, I to L, N to T), column C's cost centre will not appear.

Hence, I can do 2 checks here:

1. Check if column C shows the cost centre number
2. Columns D to G, I to L, N to T are EMPTY

Either of the above, my macro should pick up this filename, and list it on another worksheet where I run the macro.

Sorry to come back to you only now, was still trying to figure out the codes on my own.


Cheers!

[dflak]

This looks like an entirely different spreadsheet than the original.

I do have one question. What is the intent of this formula: =IF(COUNTA(D10:L10,N10:T10)>1, INDIRECT("CC"), ""). I get that you are counting up the number of data entries in cells D10, L10, N10 and T10 and if there are fewer than 2 entries, put a blank string. What is the INDIRECT("CC") supposed to accomplish? As it reads now, it returns the range pointed to by the string "CC" which does not make sense.

Also how do cells E1 and E2 relate to the Department and Cost Centers in columns C and D?

Also, unless you always have 50 entries you may want to consider putting the data into an Excel Table. Excel tables know exactly how many rows they have and they grow or shrink as you add data. You reference them by column name (intellisense helps a lot here) so your formulas always reference the exact amount of data they need, and you don't have to change them if you change the number of rows in the table.

Here is an article that will get you started with tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.