0

i have my balance sheet in excel, there are two columns i want to work on, one is installment another is date.

so if the value of installment gets decrease by one then date month should get increase by one, for example:

installment date

2 2/1/2010

then if it changes to

installment date

1 2/2/2010

installment decreases by one and date increases by one month

so how it can be done into excel through formula or by VBA

can anybody tell me ??

Pratyush Goyal
  • 5
  • 1
  • 11

2 Answers2

0

try this

place in your "balance" worksheet code pane the following code

Option Explicit

Public myVal As Long
Public installmentRng As Range

Private Sub Worksheet_Activate()
Set installmentRng = Me.Range("A1:A1000") ' <== here set the range that contains installment values
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim installmentChange As Long

Application.EnableEvents = False
With Target
    If Not Intersect(.cells, installmentRng.SpecialCells(xlCellTypeConstants)) Is Nothing Then
        installmentChange = Target.Value - myVal
        .Offset(, 1) = WorksheetFunction.EDate(.Offset(, 1), -installmentChange)
        myVal = Target.Value
    End If
End With
Application.EnableEvents = True

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target.cells, installmentRng.SpecialCells(xlCellTypeConstants)) Is Nothing Then myVal = Target.Value
Application.EnableEvents = True
End Sub

as you see, you must only define the range that contains the "installment" values in the first sub first statement, namely:

Set installmentRng = Me.Range("A1:A1000")' <== here set the range that contains installment values

from then on you can make your changes in "installment" values

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Couple of things. a) Worksheet_Activate does not trigger when a workbook is opened and that worksheet is already the active sheet; installmentRng would be **nothing**. b) there is no compensation for Target being more than a single cell; think of a row insert.c) no error control in Worksheet_Change to make sure that events are **always** turned back on. See [this](http://stackoverflow.com/questions/35025432/auto-fill-the-date-in-a-cell-when-change-is-made-into-an-adjacent-cell/35025830#35025830) for solutions on a couple of those. –  Apr 16 '16 at 21:32
  • Thanks @Jeeped for pointing that out. All those issues are easily fixable: a) moving installmentRng setting in the Select in Worksheet_SelectionChange (and in Worksheet_Change too, should installmentRng values be changed from other sheets) b) inserting some "If Target.Count=1 Then ...)" check before acting c) inserting some "On Error GoTo ErrHandler" at the beginning of the Worksheet_SelectionChange sub and inserting the label "ErrHandler:" right before the "Application.EnableEvents = True" statement. I'll let the OP take care of it should he ever need the answer yet... – user3598756 Apr 17 '16 at 05:28
  • Yes, it isn't even clear to me whether the OP wants to modify the date on an actual change or whether he/she meant when a new 'installment' line was added. In the case of the latter, there is no mention of an identifying column for the 'installment'; in the case of the former there is no mention of where the installment number or the date is. Just a bad question overall and one that likely cannot be answered with any level of accuracy with the provided information. –  Apr 17 '16 at 05:39
  • @Jeeped: actually I think OP's question was sufficiently precise: 1) _if the value of installment gets decrease_ -> no insertion 2) the "example" was _installment date_ and then _2 2/1/2010_ -> I assumed one cell aside the other. Anyhow I was intrigued by the question theme and by exploring the use of a Public variable "helper" instead of a cell one. Most of the times I answer for learning myself.... – user3598756 Apr 17 '16 at 06:06
0

I found a workable solution for you. This would require 2 dates from what I tested.

For example: A1 = the original install date and B1 = the changed install date. Then you can try this function:

=IF(B1<A1,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),DATE(YEAR(A1),MONTH(A1),DAY(A1)))

As you can see within the function, an additional month is added based on the cell you choose (A1). The easy part about this is you can add or subtract to each part of the date as needed. Hope this helps.

Nexus
  • 1