vba formula in yyymmdd

[ummjay]

hi! how can i set this formula (today+91) as a general, in yyyymmdd format? So that I can then copy and paste it over to AF:?

'Maturity formula
    Range("AM" & lastrowadj & ":AM" & lastrowfinal).Formula = "=today()+91"
    Range("AM" & lastrowadj & ":AM" & lastrowfinal).Copy
    Range("AF" & lastrowadj & ":AF" & lastrowfinal).PasteSpecial xlPasteValues
    Range("AM7:AM" & lastrowfinal).Clear
    Application.CutCopyMode = False
  
    'sort by symbol
    Range("AD7:AL" & lastrowfinal).Sort key1:=Range("AG7:AG" & lastrow), _
    order1:=xlAscending, Header:=xlNo

 

[6StringJazzer]

Your overall approach can be simplified greatly. Replace the code above with this:

'Maturity formula
    Range("AF" & lastrowadj & ":AF" & lastrowfinal).Value = Date + 91
    Range("AF" & lastrowadj & ":AF" & lastrowfinal).NumberFormat = "yyyymmdd"

    'sort by symbol
    Range("AD7:AL" & lastrowfinal).Sort key1:=Range("AG7:AG" & lastrow), _
    order1:=xlAscending, Header:=xlNo

You could just format column AF in the worksheet to have the desired date format then you wouldn't have to do it in the code.

 

[ummjay]

worked like a charm, thanks!

 

[6StringJazzer]

You're welcome, glad to hear it!

 

[ummjay]

how would I convert this to a general format?
Range("AF" & lastrowadj & ":AF" & lastrowfinal).NumberFormat = "yyyymmdd"

 

[Joe4]

This is a great example of how you can easily use the Macro Recorder to get what you need.
Turn on your Macro Recorder, and record yourself manually changing the format of any cell to "General".
Then stop the Macro Recorder and look at the code you recorded, i.e.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B3").Select
    Selection.NumberFormat = "General"
End Sub

Then, just take what you need out of it and apply it to your code, i.e.

Range("AF" & lastrowadj & ":AF" & lastrowfinal).NumberFormat = "General"

The Macro Recorder is a great little tool to get snippets of code like this.