PDA

View Full Version : automatic macro initiation under conditions



nihi
07-09-2015, 11:32 AM
Hello,

I have a table where purchases in several months are shown. When a new purchase is added to the table, i want a macro that is based on the month entered to trigger. However, I only want it to trigger if that month has not been entered before (i.e. if this is the first purchase in that particular month).

This is what i got at the moment:




Private Sub Worksheet_Change(ByVal Target As Range)



Dim Isect As Range

For i = 0 To 39

Set Isect = Intersect(Target, Cells(15 + i, 2))


If Not Isect Is Nothing And Cells(15 + i, 2).Value <> Cells(15 + i, 2).Offset(-i, 0).Value Then

Select Case Target.Value

Case Is = "Jan"
Call Jan

[repeated for all months]
End Select
Else


End If
Next i

End Sub


So the important line to change here is

If Not Isect Is Nothing And Cells(15 + i, 2).Value <> Cells(15 + i, 2).Offset(-i, 0).Value Then

At the moment, this triggers the macro for the month when it is entered, unless that month has been entered in the first cell of the table (B15), rather than for all the cells between the entry cell and the first cell.
Moreover, it also ignores entries in B15, since according to that code it believes that it has been entered before (since it is the first cell).
Since my knowledge of VBA is quite limited I have no more ideas how to make this work.

I am working with excel 2010.

Thank you for your help in advance!

nihi
07-09-2015, 03:05 PM
Is there perhaps any way that i can say:


For q=1 to 39
If Not Isect Is Nothing And Cells(15 + i, 2).Value <> Cells(15 + i, 2).Offset(-q, 0).Value Then
But stop looping through q when q=i?
I think that would do the job, however i cant end q early in the same line (right?) and i also cant move it to the next line, as it has to appear before the "Then" and "Then" and "if" must be in the same line.