# Calculate date by each row

#### carlrubber

##### New Member
Code:
``````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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### baitmaster

##### Well-known Member
in what way doesn't it work?

#### baitmaster

##### Well-known Member
Code:
``````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

##### Well-known Member
Hi,

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

Code:
``````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

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

like this?

Code:
``````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

##### MrExcel MVP, Moderator
Cross-posted here: Calculate date by each row

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

#### carlrubber

##### New Member
thz the code works perfect

Replies
2
Views
356
Replies
1
Views
226
Replies
2
Views
298
Replies
0
Views
175
Replies
13
Views
387

1,191,040
Messages
5,984,295
Members
439,882
Latest member
gerdc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back