Calculate date by each row

[carlrubber]

For j = 4 To lastrow1
If Sheet3.Cells(j + 1, "H") <> "" Then
If Sheet3.Cells(j + 1, "I") = "" Then
Range(j + 1, "I").Formula = "=DATE(YEAR([I]&j+1),MONTH([I]&j+1)+18,DAY([I]&j+1))"
End If
End If

would anyone help how to make the code works?
thz

 

[baitmaster]

in what way doesn't it work?

 

[baitmaster]

For j = 4 To lastrow1
    If Sheet3.Cells(j + 1, "H") <> "" And Sheet3.Cells(j + 1, "I") = "" Then
        Sheet3.Cells(j + 1, "I").FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+18,DAY(RC[-1]))"
    End If

 

[pjmorris]

Hi,

I ended up with this, which actually does something, but since it creates a circular reference it seems pretty pointless?

Sub test()
    Dim j As Integer
    Dim lastrow1 As Integer
    
    lastrow1 = 20
    
    For j = 4 To lastrow1
        If Sheet3.Cells(j + 1, "H") <> "" Then
            If Sheet3.Cells(j + 1, "I") = "" Then
                Sheet3.Cells(j + 1, "I").Formula = "=DATE(YEAR(I" & j + 1 & "),MONTH(I" & j + 1 & ")+18,DAY(I" & j + 1 & "))"
            End If
        End If
    Next j
End Sub

Worth noting the indenting as baitmaster has also done - it will make reading your code much easier.

HTH

 

[friel300]

You havnt given us much to go on, so this is just a guess.

like this?

Sub Macro_Name()
    Dim lastrow1 As Integer                                         'to declare the lastrow1 Variable


    lastrow1 = Sheet3.Range("H" & Sheet3.Rows.Count).End(xlUp).Row  'to populate the lastrow1 Variable


For j = 4 To lastrow1
        If Sheet3.Cells(j + 1, 8) <> "" Then
            If Sheet3.Cells(j + 1, 9) = "" Then
                Range("I" & j + 1).Formula = "=DATE(YEAR(H" & j + 1 & "),MONTH(H" & j + 1 & ")+18,DAY(H" & j + 1 & "))" 'cells refrenced was also the cell that being filled in changed ref to COL H
            End If
        End If
    Next
End Sub                                                             'Macro needs to be contained in Sub / End sub

Hope this helps

 

[RoryA]

Cross-posted here: Calculate date by each row

Please read the forum rules on cross-posting and abide by them in future. Thanks.

 

[carlrubber]

thz the code works perfect