VBA Code to AutoFill a column based on date

[sergo_wasp]

Hi everyone,

I would really appreciate if you could help me with one issue.
I need to write VBA code that is going to drag the formula down in column B depending on what date is today in column A.
If the date is today or 27.08.2022 then column B will be filled out until including B6 cell and so on.

Thank you so much!

Wish you a nice day!

sergo_wasp

 

[My Aswer Is This]

I really do not think you need a formula in the cell if you're using Vba
With this script any time you enter todays date in column A
The sum of columns H and I same row will be entered in column B same row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script will run when you enter Todays Date in column A

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/27/2022  11:33:19 PM  EDT
If Target.Column = 1 Then
    If Target.Value = Date Then Target.Offset(, 1).Value = Target.Offset(, 7).Value + Target.Offset(, 8).Value
    
End If
End Sub

 

[sergo_wasp]

I really do not think you need a formula in the cell if you're using Vba
With this script any time you enter todays date in column A
The sum of columns H and I same row will be entered in column B same row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script will run when you enter Todays Date in column A

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/27/2022  11:33:19 PM  EDT
If Target.Column = 1 Then
    If Target.Value = Date Then Target.Offset(, 1).Value = Target.Offset(, 7).Value + Target.Offset(, 8).Value
   
End If
End Sub

Thank you for your answer.

However, I showed a dummy version of my data. The real one is far more complex.

There are a lot of formulas and columns.
So the real issue for me is to write a code that is going to drag the formulas down in columns (B:Z) depending on what date is today in column A. By date I mean function TODAY()-1 in excel. So it should be totally automated.

Something like
Case when Date is today()-1
Then formula in column B dragged down to B6 based on column A

But today()-1 is static so the second line shall be dynamic.

Hope I clarified better my issue. Thanks again!

 

[Peter_SSs]

Why not build the date check into the formula, then you never have to drag it anywhere after initially filling the whole column?

22 08 28.xlsm
	A	B						H	I
1	Date
2	25/08/2022	3						1	2
3	26/08/2022	6						2	4
4	27/08/2022	9						3	6
5	28/08/2022							4	8
6	29/08/2022							5	10
7	30/08/2022							6	12
Fill to date

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2<TODAY(),SUM(H2:I2),"")

 

[sergo_wasp]

Why not build the date check into the formula, then you never have to drag it anywhere after initially filling the whole column?

22 08 28.xlsm
	A	B						H	I
1	Date
2	25/08/2022	3						1	2
3	26/08/2022	6						2	4
4	27/08/2022	9						3	6
5	28/08/2022							4	8
6	29/08/2022							5	10
7	30/08/2022							6	12
Fill to date

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2<TODAY(),SUM(H2:I2),"")

That would be a good solution but my simplification was too bad. Apologies.
There is actually a link in each cell in B not a formula.
Such as in B2 =‘\\dummy001\DUM\[db_dummy.xls]db_random’!B27
In B3
=‘\\dummy001\DUM\[db_dummy.xls]db_random’!B28

 

[sergo_wasp]

Why not build the date check into the formula, then you never have to drag it anywhere after initially filling the whole column?

22 08 28.xlsm
	A	B						H	I
1	Date
2	25/08/2022	3						1	2
3	26/08/2022	6						2	4
4	27/08/2022	9						3	6
5	28/08/2022							4	8
6	29/08/2022							5	10
7	30/08/2022							6	12
Fill to date

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2<TODAY(),SUM(H2:I2),"")

Each month that link is different so I cannot fill down the whole column with that formula you pasted.. unfortunately. Is there any other solution to this you think?

 

[Peter_SSs]

Good idea to give a fairly true snapshot of your actual requirement at the start.

Does this do what you want?

Sub Fill_Down()
  Dim rDateFound As Range
  
  Set rDateFound = Columns("A").Find(What:=Date)
  If Not rDateFound Is Nothing Then
    With Range("B2")
      .Replace What:="=", Replacement:="|=", LookAt:=xlPart
      .AutoFill Destination:=.Resize(rDateFound.Row - .Row + 1)
      .Resize(rDateFound.Row - .Row + 1).Replace What:="|=", Replacement:="=", LookAt:=xlPart
    End With
  End If
End Sub