Insert data before Offset

[Livin404]

I'm trying to add data "DATE:" to an offset in a formula. The end result would be "DATE: WEDNESDAY NOVEMBER 18 2020".

For briefness I only included a couple of lines which I believe where the edit should go. I tried using different variations of adding this piece

.Resize(1).Offset(-1).Value = "DATE:"

with "&", and it either errored out or inserted "DATE:" and ignored the actual date.

For x = .Areas.Count To 1 Step -1
      If x > 1 Then .Areas(x)(1).EntireRow.Insert
      With .Areas(x)
        .Resize(1).Offset(-1).Value = .Value
         .Resize(1).Offset(-1).Replace "(*", Year(Now), xlPart, , , , False, False
        .Resize(1).Value = "DESTINATION"

 

[My Aswer Is This]

You would need something like this:

Sub Insert_Date()
'Modified 11/18/2020  11:33:20 PM  EST
Cells(1, 1).Value = "Date:" & Format(Date, "DDDD MMMM DD YYYY")
End Sub

 

[Livin404]

You would need something like this:

Sub Insert_Date()
'Modified 11/18/2020  11:33:20 PM  EST
Cells(1, 1).Value = "Date:" & Format(Date, "DDDD MMMM DD YYYY")
End Sub

I appreciate your input but I needed the "DATE:" in front of the date. It needs to be part of this VBA which I got from a gentleman on this site. I think it should be on the same line as :

.Resize(1).Offset(-1).Replace "(*", Year(Now), xlPart, , , , False, False

Thank you.

Sub Outbound_Fids()
  Dim x As Long
  With Sheets("Outbound FIDS").Range("A2", Sheets("Outbound FIDS").Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants)
    For x = .Areas.Count To 1 Step -1
      If x > 1 Then .Areas(x)(1).EntireRow.Insert
      With .Areas(x)
        .Resize(1).Offset(-1).Value = .Value
        .Resize(1).Offset(-1).Replace "(*", Year(Now), xlPart, , , , False, False
        .Resize(1).Value = "DESTINATION"
        .Offset(1).Replace "*(", ""
        .Offset(1).Replace ")*", ""
      End With
    Next
  End With
End Sub

 

[Fluff]

Maybe

.Resize(1).Offset(-1).Value = "DATE: " & .Value

 

[Livin404]

Thank you for your response, I tried right after the first resize line and in-place of that line and I ended up getting a type mismatch error run-time error 13. I'm sure the line that has to be adjusted is one of the two resize lines.

 

[Fluff]

I'm sure the line that has to be adjusted is one of the two resize lines.

Well if you don't know, how would we? Only you can see your data.

 

[Livin404]

I understand, I tried using "Watch" and there looks like there is some sort mismatch on either side of the equals. In any case I provided an image of what it started out with. Never mind the cell fills and borders they're from another VBA. The word DATE followed by a space "DATE: " I hope to get in front of the date as seen in the image DATE: THURSDAY NOVEMBER 19 2020.
I have one other question after I/we sort this out. I will need to have those dates in yellow and aligned to the left. Would it be better to do it while I'm in this code or should I create a new VBA to sort that out, or is it even an option. Thank you,

 

[Fluff]

What happens when you run the code?

 

[Livin404]

What happens when you run the code?

I get that mismatch error, then I selected the debug and you see the results in the image. Thank you for the quick response.

 

[Fluff]

Do you have two or more contiguous cells that have data in them?