Select cell based on first of month date

**mexcel300** · 04-15-2011, 02:09 PM

I have a range of dates in column B of my spreadsheet. I have the Today() function in E2. I would like to know how to use vba so that when it is the first of the month in E2, a function will run that will clear 6 cells to the right of that date, and run before any values are entered into E3:E5. After the initial clear, I would like for it to be able to take input from E5:E6. Thanks for any assistance, Mike.

**watersev** · 04-15-2011, 03:19 PM

hi, Mike, please check attachment

**mexcel300** · 04-15-2011, 04:52 PM

Hi watersev, thanks for the input, however, it doesn't let me enter values on the row for the first date using the input form. It seems to add numbers correctly after that row, i.e. 4/2/11 but not on the 1st of the month row. I want it to clear the numbers on the first day because the way the sheet is setup, the numbers sum down continuously and that row(4/1/11) will have March's total in there so I won't be getting an accurate sum of what is spent each month. I want to start with fresh numbers each month. Hope this makes sense.

**watersev** · 04-15-2011, 06:06 PM

check attachment, the code will only clear off 6 cells to the right of the first month date cell. Then you can add data through the add button.

**mexcel300** · 04-15-2011, 06:36 PM

Checked it out. It clears the cells but it is still taking the totals from D18,F18, and H18 and putting them into D19, F19, and H19. It is those particular columns that have the running totals for each expenditure that need to be cleared, and somehow stay cleared after data is input in the form on the 1st day of the month so that it starts a new running total each month. The main idea is that I would like to have totals just for the particular month at the end of each month. Maybe the idea I have isn't the best approach so I'm open to suggestions to accomplish that. Thanks for the continued help watersev.

**watersev** · 04-16-2011, 03:52 AM

please check attachment, on the first day of each month it will start summing up values from zero

**mexcel300** · 04-16-2011, 11:40 AM

That seems to remedy the change of the month, but now when I go to enter numbers from the month before, for example March 31 and add them in, I get a runtime 91 error object variable or with block variable not set. If I was on say April 5th, but forgot to record an entry for March 31st, would it be possible to add a value for a previous month or is that getting too complex?

**watersev** · 04-16-2011, 12:02 PM

yes, you get that error because column B has no 1 of March date. If all the dates are in chronological order there will be no mistake.
How the code works: it checks for the row where 1 day of the month of E2 is located and uses that row for sum total. I can add error handler for this particular issue to make it more friendly.
If all March dates are in column B you would not get error message.
For this code to work column B must have 1st dates of all months you will enter data for.

**mexcel300** · 04-16-2011, 06:15 PM

I will only use full months so don't worry about the error handler. I really appreciate your help with this. It gives me an idea on how vba works for some of these things as I am trying to learn the basics. Now I should be able to get this budget sheet functioning right. Thanks again watersev! Mike