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

Board Regular
Joined
May 30, 2018
Messages
56
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Does this do what you want?

Code:
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.
 
Upvote 0
SWEET that worked GREAT thank you, glad you made that look easy. Working on another issue now...
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top