Automatically updating formula

[stewart1]

Hi,

I hope someone can point me in the right direction with this.

I have managed to combine this code so it works fine

Please Login or Register  to view this content.

I have combined it with this:

Please Login or Register  to view this content.

However I want to be able to use the first code again to work with a different column (Q). This is how I have changed it.

Please Login or Register  to view this content.

I cannot get the second code to work no matter what I do. Basically with the Q column
when this bit of code is fired up by me putting in data

Please Login or Register  to view this content.

the date is put in. Once the date has gone in column "P", that's when I need

Please Login or Register  to view this content.

to kick in.

I am no master but I have had a right good go and I cannot solve it.

I have posted (see below) but to no avail.

Please someone point me in the right direction.


Thanks.


Stewart

http://www.mrexcel.com/forum/showthread.php?t=536729

[stewart1]

Cheeky bump.

Anyone think it can be done?

Thanks

[stewart1]

Okay, could anyone tell me how I can put this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(3))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("A" & i - 1 & ":B" & i - 1).Copy Range("A" & i & ":B" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub

with this

On Error Resume Next
Set c = Intersect(Target, Columns(16))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(1, 0)) Or Not IsEmpty(c.Offset(-1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("Q" & i - 1).Copy Range("Q" & i)
Application.EnableEvents = True
On Error GoTo 0

So that they both work.


Thanks

[stewart1]

Another bump.

Desperately need this to work.

Thanks

[TMS]

Use Union. For example:

Please Login or Register  to view this content.

So:

Please Login or Register  to view this content.

Then check for the specific columns to determine which action to take.


Regards

[stewart1]

Thank you for your reply,

I was beginning to lose hope,

Been waiting ages for some direction,

Please could you or anyone else tell me how I fit this into the two codes.

Thanks

[TMS]

I'm not sure if you're problem isn't just that column 16 is column P, not column Q.

A simple way to combine the two sets of code without having to change much is as follows:

Please Login or Register  to view this content.

You can only have one Worksheet Change Event so this one monitors columns C and Q. If there's a change in either of those it calls the relevant subroutine for that column and passes across the Target cell. The subroutines have been renamed to reflect the column they relate to.

Note also that the column *number* is changed to 17 which is column Q.

Regards

[stewart1]

Hi TMshucks,

Thanks for getting back to me. This is something I have never seen before, being such a novice and all! Most help code that get's posted is for only one event. (I sat trying to do it for about ten hours in all; sad man!) At least now I can see another way of combing code. And you are right, it was column "Q".

I've now got to find a way of putting conditional formatting VBA into column Q to show red amber green for the numbers that are entered, as this will be put on excel 2003.

Thanks again,

Stewart

[TMS]

I guess that, if I were starting from scratch, I might try to have all the code in the one Worksheet Change Event but, as you already have the code working as you need it, it seems a shame to throw it away.

The other advantage is that, if you were to need a similar routine for a third or fourth column, you already have the building blocks.

Regards ... thanks for the feedback.