VBA code for Date 05/30/2018 in one column to fill another column with Text and FY based on year in Date Column

[CLCoop]

Spreadsheet name SOFData to auto update Column A (Starting in A2) to reflect Fiscal Year (IE FY18 Budgeted Amount) based on what Column F shows (starting at F2) which has a date in US format MM/DD/YYYY

Column F2:F has dates in US format for example: 05/30/2018 (MM/DD/YYYY)
Column A2:A is blank

need Column A2 to look at column F2 date IE 05/30/2018
based on F2 date Column A2 should update to show: "FY18 Budgeted Amount"

need Column A3 to look at column F3 date IE 05/01/2019
based on F3 date Column A3 should update to "FY19 Budgeted Amount"


This is the code I tried. nope didn't work, it did however populate any and all BLANK columns in the entire spreadsheet to say Budgeted Amount FY. EEK!!

Dim ws As Worksheet, lRow As Long, r As Range
Set ws = ThisWorkbook.Sheets("SOFData")
With ws
lRow = .Range("F" & .Rows.Count).End(xlUp).Row
For Each r In .Range("A1:A" & Row)
If IsEmpty(r) Then
r.Formula = "=If(F" & r.Row & "<>"""",""Budgeted Amount FY"","""")"
r = r.Value
End If
Next
End With

[/code]

The really lost and confused CL Coop

 

[Scott T]

Does this do what you want?

Sub fy()
Dim lr As Long
Dim ws As Worksheet
Set ws = Sheets("SOFData")
lr = ws.Cells(Rows.Count, "F").End(xlUp).Row
For x = 2 To lr
    If IsEmpty(Cells(x, "A")) Then
        fy = Right(Year(Cells(x, "F")), 2)
        Cells(x, "A") = "FY" & fy & " Budgeted Amount"
    End If
Next x

End Sub

Note in the code you posted use used lRow as a variable but did not use it in your code as everything was Row.

 

[CLCoop]

SWEET that worked GREAT thank you, glad you made that look easy. Working on another issue now...