VBA Code to auto date cells

[data808]

I have a merged C2 and D2 cell where I will type in the month "January" for example and another cell G2 that I will type in the year 2022. I will do this for every month. Is there a way based on which month and year I type into these cells, to auto populate 1/1/22 - 1/31/22 from cells A4 - A34? I realize that not every month has 31 days and so if these happens for February for example and goes to 2/31/22, that is ok. I am just trying to create a template and we will only be using the dates that we need to and ignore the dates and don't exist. Thanks.

 

[My Aswer Is This]

Vba never likes using Merged Cells.
Why not just enter January in just one range like C2?

 

[data808]

Vba never likes using Merged Cells.
Why not just enter January in just one range like C2?

Sorry had to do it for cosmetics so the spacing looks better. I tried VBA just to get started but its not working:

Sub auto_date_cells()

If Range("C2:D2").Value = "1" Then
Range("A4").Value = "01/01/22"
End If


End Sub


I still haven't added the year G1 cell into the code yet but just wanted to see if this would work but it does nothing. Do you know why? I added the VBA to sheet1 and then I tried it in a module with no luck.

 

[My Aswer Is This]

Sorry had to do it for cosmetics so the spacing looks better. I tried VBA just to get started but its not working:

Sub auto_date_cells()

If Range("C2:D2").Value = "1" Then
Range("A4").Value = "01/01/22"
End If


End Sub


I still haven't added the year G1 cell into the code yet but just wanted to see if this would work but it does nothing. Do you know why? I added the VBA to sheet1 and then I tried it in a module with no luck.

I know users do this sometimes for cosmetic reasons, but Vba does not like this.

If you just enter 1 then why do you need two ranges.

 

[data808]

I know users do this sometimes for cosmetic reasons, but Vba does not like this.

If you just enter 1 then why do you need two ranges.

This was a test VBA. If you look at my original post I will be typing in the whole month name. I just wanted to see if I could get the VBA to somewhat work.

 

[My Aswer Is This]

This was a test VBA. If you look at my original post I will be typing in the whole month name. I just wanted to see if I could get the VBA to somewhat work.

You may get another answer from another poster here with an answer. But from my experience Vba does not like Merged Cells

 

[data808]

You may get another answer from another poster here with an answer. But from my experience Vba does not like Merged Cells

Thanks for the replies. I've definitely gotten VBA to work with merged cells so I know its possible. Just not sure why I can't get even my test VBA to work. I think you could even reference the merged cell with the starting from the first one so the code would look like this:

Sub auto_date_cells()

If Range("C2").Value = "1" Then
Range("A4").Value = "01/01/22"
End If


End Sub

I just basically got rid of the D2 so it went from "C2:D2" to just "C2". I have gotten similar codes to work like this before. Just don't know why this isn't working. Again thanks for trying to help. Hopefully someone on here knows how to get this done.

 

[My Aswer Is This]

Thanks for the replies. I've definitely gotten VBA to work with merged cells so I know its possible. Just not sure why I can't get even my test VBA to work. I think you could even reference the merged cell with the starting from the first one so the code would look like this:

Sub auto_date_cells()

If Range("C2").Value = "1" Then
Range("A4").Value = "01/01/22"
End If


End Sub

I just basically got rid of the D2 so it went from "C2:D2" to just "C2". I have gotten similar codes to work like this before. Just don't know why this isn't working. Again thanks for trying to help. Hopefully someone on here knows how to get this done.

Yes I was going to suggest formatting the two cells as Merged and then Center Text
But then we would use Just C2 for example in the script.

 

[data808]

Yes I was going to suggest formatting the two cells as Merged and then Center Text
But then we would use Just C2 for example in the script.

I just tried a similar code on a brand new spreadsheet with no merged cells and still nothing. Here is the code I tried:

Sub test()

If Range("A1").Value = 1 Then
Range("B1").Value = 5
End If

End Sub

Any ideas why this isn't working? So I type "1" into A1 cell and nothing happens with B1 cell.

 

[MARK858]

Any ideas why this isn't working? So I type "1" into A1 cell and nothing happens with B1 cell.

If you want it to work as you type then you need to put it to be a Worksheet_Change event/sub not a regular Sub