trying to write a very simple subroutine and having a hard time

EmeraldEngineer

New Member
Joined
May 7, 2014
Messages
7
I'm attempting to write a simple subroutine to determine whether or not cel (E4) has a date in it and if it does then populate cell (F4) with that date plus 14 days and cell (G4) with that date plus 28 days. I would also like the sub to run once the date is entered in cell( E4). Look below to see what I've attempted so far and try to hold back your chuckling ha. I'm obviously not very experienced with vba but would really appreciate it if someone could help me out. Thanks.

Private Sub Natalie()
Dim Number_1 As Integer
Dim Number_2 As Integer

Number_1 = 14
Number_2 = 28

If Worksheets("Reports 1-3").Range("F4").Value Then
Worksheets("Reports 1-3").Range("F4").Value = ""
Worksheets("Reports 1-3").Range("G4").Value = ""
Else
Worksheets("Reports 1-3").Range(F4).Value = Worksheets("Reports 1-3").Range(E4).Value + Number_1
Worksheets("Reports 1-3").Range(G4).Value = Worksheets("Reports 1-3").Range(E4).Value + Number_2
End If
End Sub
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try replacing:

Code:
If Worksheets("Reports 1-3").Range("F4").Value Then

with:

Code:
If Worksheets("Reports 1-3").Range("E4").Value = "" Then
 

EmeraldEngineer

New Member
Joined
May 7, 2014
Messages
7
Nice catch! ha. But unfortunately it still doesn't want to work. It gives me a subscript out of range error 9.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Do you have a worksheet named "Reports 1-3" in the ActiveWorkbook? Where did you put your Sub?
 

EmeraldEngineer

New Member
Joined
May 7, 2014
Messages
7
Oh great! That did it! Thank you! Now all i need to do is figure out how to make it run when the value of cell E4 is changed.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,407
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top