VBA code to autopopluate FY year in Column A when date field is in Column Q

[CLCoop]

When you open the spreadsheet there is no date in Column Q. I've put in code to auto fill Column Q with a Now() structure. I then coded Column A to look at Column Q and if empty to take the date in Column Q and to make Column A to show "FY18 Budget Authority" based on the date in Column Q. When I run the code I get NO errors, Column Q does populate with the date in MM/DD/YYYY format but Column A does nothing, stays empty. I'm sure there is better way to write both of the code please help.

'This part put in the date based on todays date (wish it would only fill in date if B Column has information in it)
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Date"

date_test = Now()
Range("Q2:Q300").Select
Range("Q2:Q300") = Format(date_test, "yyyy/mm/dd")
On Error Resume Next

If Range("f2").Value = "" Then
For Each ws In ThisWorkbook.Sheets
ws.Range("f2").Value = Date
Next ws

'code for Column A to look at column Q to fill with FY18 Budget Authority
Set ws = Sheets("SOFData")
lr = ws.Cells(Rows.Count, "Q").End(xlUp).Row
For x = 2 To lr
If IsEmpty(Cells(x, "A")) Then
FY = Right(Year(Cells(x, "A")), 2)
Cells(x, "A") = "FY" & FY & " Budget Authority"
End If
Next x

[/code]

 

[Fluff]

Try

FY = Right(Year(Cells(x, "[COLOR=#ff0000]Q[/COLOR]")), 2)

 

[CLCoop]

Ugh did the change and Column is still empty


Set ws= Sheets("SOFData")
lr = ws.Cells(rows.Count, "q")),2)
For x = 2 to lr
If IsEmpty(Cells(x,"A")) then
Cells(x, "A") = "FY" & fy & " Budget Authority"
End If
Next x

Erase arr

any other suggestions... hoping to finish this project today. Thank your eyes, talent, and time.

 

[Fluff]

Make sure that the cells in col A are actually empty.

 

[CLCoop]

Oh my goodness the formula was prefect however in an earlier code I had auto populated A1 to show heading so this prevented the code from working right. I moved the header auto fill to after the above code to make sure the column was empty and it all worked out. Thanks for the second set of eyes to see something so right there.

Now I can have a good night sleep knowing this 2 day process has now been automated to 32 secs

 

[Fluff]

Glad to help & thanks for the feedback

 

[CLCoop]

Ok it worked so I saved in exited out then when back in and now Column A won't populate but it is 100% empty to include header. Is there a way to avoid the IF ISEmpty and have it override regardless?

 

[Fluff]

Just remove that line & the End If line