[EXCEL VBA] Insert a value or formula into empty cells in a range

[Stuart_C]

I'm just looking at a spreadsheet for our personnel department and after having heard a quick grumble from another member of staff want to make it a bit easier for the end user.

 

It's a timesheet where the user fills in when they are in, off ill and so on (Image attached). To minimise the work of the end user I want to have them fill in the Off/Sick etc then have a button that looks at all the cells in the range and fills in the "I" in any cell that doesn't contain a value already. if possible I would also only like it to put values in cells where there is a member of staff and an actual day (i.e Febuary will only go up to the 28th and whilst my spreadsheet will automatically remove the 29/30/31 if if you set the month to "2" I don't want "I"'s what will be columns AD-AF

 

Any suggestions would be gratefully received.

[Steve21]

Two questions, a) Can you upload the sheet (If it's not got private data), easier to edit on it than remake it

 

and b) When you say "in the range" are you only doing 1 row at a time? or "every" cell in the black line.

 

Steve

[Steve21]

Test this and see if it works, Will need to change a few settings in it like where your table starts etc but should work as you want.

 

Will fill in all blanks, only if the date exists above it, and will stop if no user name present.

 

test for stu.zip

 

(Had to Zip it due to forum blocking macro enabled worksheets (and remember to unblock macros )

 

Steve

[Stuart_C]

Cool. I'lll have a quick look this afternoon, got to do some other work first rather than haveing fun with Excel