Macro for Filling the empty cells based on current date.

Dear Friends,

I have a spreadsheet for entering Employee's details. On that I am entering employees daily attendance status. It is almost 600 employees and entering each and every employee is a herculean task. So What I need is, I can enter on the Absent, Casual Leave, and etc...and the remaining unmarked staffs will be PRESENT. So that If I have a command button for that, when I click that button it should automatically apply "P" on the remaining cells on that particular date's column. More clearly, I have 31 columns for each days in month and on each column's 7th ROW contains that particular day's date. So the macro has to search the empty CELL's between current date's particular column and fill it with "P". The empty cells will be between 8th row to 500th row on each day's column. One more thing the macro has to check. The empty cell on each day has to fill ONLY IF that cells respective "B" cell having any value. More clear I am entering Employees name in the "B" Column from 8 th to 500th row. So, After clicking the command button, macro has to find that particular date containing column and find the empty cells between that column's 8th ROW to 500th ROW and has to fill those empty CELLS with "P" ONLY IF there is any name in the B column.

Kindly Suggest me a macro for this.

why even bother to put in anything for p? you could probably work out a formula that will count blanks, just as easily as it could count p's

aneshdas, try this. Also, you might want to think about not hiding rows that are a date in the future.

Please Login or Register  to view this content.

Greg

Originally Posted by gsnidow

aneshdas, try this. Also, you might want to think about not hiding rows that are a date in the future.

Please Login or Register  to view this content.

Greg

Sir, Thanks a lot for your reply. Two Errors are there in your macro sir.
1. First this macro is filling "P" only in the cells which's respective "B" cell is empty. Iwant it in vice versa. That is if No value in B no value should be in the respective date's cell.
2. Also this macro is changing all the Cells in the dates column in to "P". So this will clear the values which I already entered. More clearly, I am already entered the "A", "L", etc in some of the cells in the current date column. I do not want to change the values on those cells. So, Simply what the macro I required has to do is, Replace the EMPTY cell's in the Current date's column with "P" ONLY IF there is any value in their respective "B" Cell....Sir, hope you get my point and will help me accordingly.

Ok, try this...

Please Login or Register  to view this content.

Originally Posted by gsnidow

Ok, try this...

Please Login or Register  to view this content.

Heyhey :-)..... Thanks a lot sir.it is working..... Hurray... :-)

Sir, But it is taking tooooooooooooooo much time to process. Can you advice how to shorten this process? Since I have a lot of employees this process is giving me headache.

Sir, I have already a macro for only displaying the column which having the current date. Is there anyway to calculate with that sheet only......Even though we applied the Screen updating= false, the process is going on going on.................. please advice me how to sort this issue.

With in this macro I hope you got the logic of what I need.

Is there some other code running when you file this macro? I mean, it is only hiding a few columns, then iterating through a 500 cell range on only 1 column. This code alone should not be taking so long. Do you have formulas in lots of cells? Could you attach the workbook?

Sir, I AM USING NO EXCEL FORMULAS IN MY WORK SHEET EXCEPT SOME MACROS. I am using a bunch of macros in this spreadsheet. But nothing is connected with this macro. And the macro which I posted was running in no time. Also the macro which you provided before the last one was also running without taking any time. Only the macro which you provided was taking much time. More over that apart from the column which contains current date has already hided while opening the workbook itself using another macro. So no need to hide it again. And no other macro's are running at the same time when the macro provided by you runs. So sir, kindly check it and please provide me a simple form of this which can run in no time. Thanks in advance sir. Also the work contains a lot of datas and I am trying to make a copy of the same for your ready reference. Kindly work on this sir.

You can do it without the Len() validation, but it should not make much of a difference. I made a mock up of what I think your worksheet looks like, even filling names in column b for 1000 rows. The macro runs in mere fractions of a second. If it is taking a long time on your workbook, please let us see what you are working with by attaching a de-sensitised version of it.

Please Login or Register  to view this content.

Greg

aneshdas, on my machine, Windows 7 32bit i5, 4G ram, it literally takes fractions of a second to run. I'm kind of stumped as to why it would be taking so long on your machine.

Greg