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

Timmo7410

New Member
Joined
Nov 30, 2013
Messages
13
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Timmo7410

New Member
Joined
Nov 30, 2013
Messages
13
Never mind guys figured that one out. Was fairly simple, not sure why it wasn't working previously.
 

Cindy Ellis

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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

New Member
Joined
Nov 30, 2013
Messages
13
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802

ADVERTISEMENT

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

New Member
Joined
Nov 30, 2013
Messages
13
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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

New Member
Joined
Nov 30, 2013
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,872
Members
413,945
Latest member
V51773

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top