Visual to automate formula

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

I am looking for a way to place a formula in h and I in the corosponding row
if there is data in A.

These are my two formulas
=A3-WEEKDAY(A3,2)+1
=A3-DAY(A3)+1

So if there is data in A3 the first formula will be placed in H3 and the
second in I3. If there is no data in A3 there will be nothing in these cells.

Thank you
 
Sub DepositFormula()
Dim r1 As Range, r2 As Range, r3 As Range, n As Long
Dim i As Long
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To n
Set r1 = Cells(i, 1)
Set r2 = r1.Offset(0, 7)
Set r3 = r1.Offset(0, 8)
If IsEmpty(r1) Then
Else
r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1"
r3.Formula = "=A" & i & "-DAY(A" & i & ")+1"
End If
Next
End Sub
 
I took a placed the below under sheet one in the visual basic window and it
didn't seem to work. I tried this on a new book.

Thank you
 
Did you use Tools --> Macro --> Macros to try to run it? It worked for me.

However, if you want a solution that fills out the formulas when something
is entered into/changes in column A, then try this in the worksheet's code
module (open the workbook, select the sheet, right-click on the sheet name
and choose View Code from the list, copy and paste the code below into the
new module.

I didn't change the other very much, so actually it's doing more work than
it probably has to, but it does work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r1 As Range, r2 As Range, r3 As Range, n As Long
Dim i As Long
If Target.Column <> 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To n
Set r1 = Cells(i, 1)
Set r2 = r1.Offset(0, 7)
Set r3 = r1.Offset(0, 8)
If IsEmpty(r1) Then
Else
r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1"
r3.Formula = "=A" & i & "-DAY(A" & i & ")+1"
End If
Next

End Sub
 
Thank you very much

JLatham said:
Did you use Tools --> Macro --> Macros to try to run it? It worked for me.

However, if you want a solution that fills out the formulas when something
is entered into/changes in column A, then try this in the worksheet's code
module (open the workbook, select the sheet, right-click on the sheet name
and choose View Code from the list, copy and paste the code below into the
new module.

I didn't change the other very much, so actually it's doing more work than
it probably has to, but it does work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r1 As Range, r2 As Range, r3 As Range, n As Long
Dim i As Long
If Target.Column <> 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To n
Set r1 = Cells(i, 1)
Set r2 = r1.Offset(0, 7)
Set r3 = r1.Offset(0, 8)
If IsEmpty(r1) Then
Else
r2.Formula = "=A" & i & "-WEEKDAY(A" & i & ",2)+1"
r3.Formula = "=A" & i & "-DAY(A" & i & ")+1"
End If
Next

End Sub
Click to expand...
 
Back
Top