Updating a cell based on contents of two other cells

[jonco]

I need a routine that will add so many days to another cell depending on the
contents of a particular cell.
I have a cell (J2) that has W (for weekly), B (for Bi Weekly), or M (for
Monthly). When cell N2 (Last date) is updated with a new date, I need to
add 7 days if J2 has a W, or 14 days if J2 has a B or 30 Days id J2 contains
an M. and put the new date in cell L2 (the new due date),

Cell L2 contains the Due Date.... the one that needs to be updated after the
calculation
Cell J2 contains the info to base the calculation on W, B, or M
Cell N2 contains the old due date that is used to start the calculation

Any help is greatly appreciated.

Jonco

 

[Simon Lloyd]

This should be what you need just paste it into the Sheet code module, I
must tell you that the letter that appears in J2 must be upper case if
you want it to be lower case just change it in the code, i'm sure
someone will have a smarter way for you in this forum.

Regards,
Simon
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("J2").Text = "W" Then
Range("L2") = Range("N2").Value + 7
ElseIf Range("J2").Text = "B" Then
Range("L2") = Range("N2").Value + 14
ElseIf Range("J2").Text = "M" Then
Range("L2") = Range("N2").Value + 30
End If
End Sub

 

[Ken Johnson]

Hi jonco,

Here's another way, but I don't know that it is smarter.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$2" Or Target.Address = "$J$2" Then
Dim iDays As Byte
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
Select Case UCase(Range("J2").Value)
Case "W"
iDays = 7
Case "B"
iDays = 14
Case "M"
iDays = 30
End Select
Range("L2").Value = Range("N2").Value + iDays
Application.EnableEvents = True
Exit Sub
ERRORHANDLER:
Application.EnableEvents = True
End If
End Sub

Ken Johnson

 

[jonco]

Thanks guys, I'll give these a try.

Jonco

 

[Ken Johnson]

You're welcome jonco, thanks for the feedback.

Ken Johnson