Mathematical function on Cells(Target.Row, "")

[Timmo7410]

Hi Guys,
Have the following line of code that I would like to add 1 to the result of.

Cells(Target.Row, "F") = Cells(Target.Row, "E") - Cells(Target.Row, "D")

I've tried a number of ways to get this to work with no luck. Any thought's would be great.
It forms part of the following code that I have, just FYI

--------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("D16:D100", "E16:E100")) Is Nothing Then
Call DateCheck
End If
If IsEmpty(Cells(Target.Row, "D")) = False And IsEmpty(Cells(Target.Row, "E")) = False Then
'If Cells(Target.Row, "E") > Cells(Target.Row, "D") Then
Cells(Target.Row, "F") = Cells(Target.Row, "E") - Cells(Target.Row, "D")
End If
'End If
End Sub
---------------------------------------------
*note: I've taken out the chk to determine if the EndDate is earlier than the StartDate (i.e. negative number), as this didn't seem to work.

 

[Timmo7410]

Never mind guys figured that one out. Was fairly simple, not sure why it wasn't working previously.

 

[Cindy Ellis]

The code above "works" in that it does the math correctly, even if you put a +1 at the end of the formula. What might be the unexpected behavior is that since the triggering event is Selection.Change, and since the code only acts on Target.row, you have to select another cell in the same row for the value in F to to updated. It seems that this formula could be very easily done in the worksheet (without a macro) just by putting in the formula and copying to the applicable rows, using "iferror" to trap "N/A". You could also use "If" in that context to only do the math if the enddate is later than the start date.
Hope that helps a bit...please post back if you would like help with the formula approach, or if I misunderstood the problem.

 

[Timmo7410]

Thanks for the prompt reply Cindy,

I had just come to that conclusion myself in the meantime and am still wondering how I ended up so far into the macro, when it could have been more easily accomplished in the cell.
An additional problem with the macro, that I think you spotted, was that it would only run once. Therefore, if a user revised a Start or End date, the macro would not re-run the section and update column F.

The only reason I would have preferred the macro undertake the work would be if a user entered and EndDate that was earlier than a StartDate, thus generating a negative number. With the macro, I could advise through the use of a MsgBox that they need to re-enter their dates.

Thanks for your help, none the less.

Tim.

 

[Cindy Ellis]

You could use an "if" to determine whether the start date is before the end date, and if not, the value displayed in the cell could be your advice message. Something like (in F1):
=IF(VALUE(E1)-VALUE(D1) >= 0, E1-D1+1, "Start Date must be earlier than End Date")

Hope that helps,

 

[Timmo7410]

Hi Cindy,

I came up with the following code, which seems to work Ok, but reminded me why I started pursuing the macro option. With this code and the code you offered, I always have a value displaying in column F which is linked to another part of the spreadsheet.
Is there a way to only have the formula operate if there are values in Column D and Column E ?

Thanks again.

=IF(E16>=D16,(E16-D16)+1,"Please try again")

 

[Cindy Ellis]

Yes...just count the number of values:
=IF(COUNT(D1:E1)=2,IF(VALUE(E1)-VALUE(D1) >= 0, E1-D1+1, "Please Try Again"),"")

 

[Timmo7410]

Thanks for the reply again Cindy,

Solved it with this one in the meantime.

=IF(AND(D16<>"",E16<>""),IF(E16>=D16,(E16-D16)+1,"Please try again !"),"")

Thanks again for all your help though, I have learnt a lot.