Hello, I often need to fill cells with a date of production. What kind of a macro would be usefull in this situation so that I could just select the cells that I need to fill and just enter the date of productions?

Depends on your requirement, If you want to enter todays days, you can press control and ; I If the cells location is same and scattered across the sheet, then you can write a macro that looks at those cell and put the date there

How to fill selected cells with given data? [Solved]

Jussi
Feb 26, 2010 at 07:24 AM

Thanks for such a quick reply!
My situation is the latter this time, so the cells are scattered all over the sheet and I do not give todays date, usually yesterdays...

I have no idea what that macro would look like.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Jussi
Feb 26, 2010 at 07:59 AM

Again depends on you situation

If cells can be any place in the sheet and each time at a different location, then either you have to type in the date or have to define the address of each cell on a sheet from where macro would know what the address to target.

Jussi > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 09:52 AM

Could I first select the cells that I need to fill and that way show to macro what cells needs to be updated with the given data (the date in this case).

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Jussi
Feb 26, 2010 at 12:42 PM

Yes possible

Here is a macro. Give it a shot, but make sure that you have a backup before hand. Select the target cells first and run the macro

Sub fixDate()
    
    Dim vValue As Variant
    
    'default date is yesterday
    vValue = InputBox("Enter Date", "Date", Date - 1)
    
    
    For Each cell In Selection

        cell.Value = vValue 
    
    Next
    
End Sub

Jussi > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 12:54 PM

Thank you so much, this seems to work just as I tried to explain.
I dont know how to thank you enough.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Jussi
Feb 26, 2010 at 12:56 PM

Easy. Vote democrats. Just stay away from conservatives :P

Kidding. Glad to be of some use

Jussi > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 01:03 PM

Sorry
Our parliamentary system here in Finland works a bit differently but I'll keep your advice in mind for the future.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Jussi
Feb 26, 2010 at 02:04 PM

I just noticed some thing in the macro. If you press cancel, it would wipe out all the data of selection. Not a good idea.

Use this. It will confirm before wiping out

Sub fixDate()
    
    Dim vValue As Variant
    
    'default date is yesterday
    vValue = InputBox("Enter Date", "Date", Date - 1)
    
    If (vValue = "") Then
        confirm = MsgBox("Do you wish to wipe out data from selection ?", vbQuestion + vbYesNo)
    
        If confirm <> vbYes Then Exit Sub
        
    End If
    
    For Each cell In Selection
    
        cell.Value = vValue
    
    Next
    
End Sub

Jussi > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 02:59 PM

Ok, that didnt even cross my mind, as I'm usually filling empty cells.
Thanks.
My god our hockey team is getting murdered in Vancouver.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Jussi
Feb 26, 2010 at 03:18 PM

I know how you feel. I felt same with Reg Wings lost stanley cup last year

Jussi > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 04:10 PM

Well, they did win the stanley cup the year after :)
With Valtteri Filppula, first finn to play for Detroit.

How to fill selected cells with given data?

2 replies

Subscribe To Our Newsletter!