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

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,724
Office Version
365
Platform
Windows
Try
Code:
FY = Right(Year(Cells(x, "[COLOR=#ff0000]Q[/COLOR]")), 2)
 

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,724
Office Version
365
Platform
Windows
Make sure that the cells in col A are actually empty.
 

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,724
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,724
Office Version
365
Platform
Windows
Just remove that line & the End If line
 

Forum statistics

Threads
1,085,513
Messages
5,384,115
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top