Quick VBA Worksheet Change Event or Selection Question:

[Damil4real]

Quick VBA Worksheet Change Event or Selection Question:


I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.


In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.


The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.


So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?


Example:


If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above) into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.


I thank you for your patience and assistance!

 

[Bernie Deitrick]

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range
If Intersect(Target, Range("P")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myC In Intersect(Target, Range("P"))
Range("Q" & myC.Row).Value = Date - Target.Value
Next myC
Application.EnableEvents = True
End Sub


Quick VBA Worksheet Change Event or Selection Question:


I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.


In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.


The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.


So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?


Example:


If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above) into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.


I thank you for your patience and assistance!

 

[Luke M]

'Macro based on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P"), Target) Is Nothing _
Or Target.Count > 1 Then Exit Sub

'Don't do anything if cell is blank
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

'Create value:
Target.Offset(0, 1).FormulaR1C1 = _
"=IF(RC[-1]="""","""",TODAY()-RC[-1])"

Application.EnableEvents = True
End Sub

 

[Damil4real]

'Macro based on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P"), Target) Is Nothing _
    Or Target.Count > 1 Then Exit Sub

'Don't do anything if cell is blank
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

'Create value:
Target.Offset(0, 1).FormulaR1C1 = _
    "=IF(RC[-1]="""","""",TODAY()-RC[-1])"

Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Quick VBA Worksheet Change Event or Selection Question:

I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.

In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.

The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.

So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?

If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above)  into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.

I thank you for your patience and assistance!

.- Hide quoted text -

- Show quoted text -

Bernie & Luke, I thank you very much for the code! Worked like a
charm!!

 

[Bernie Deitrick]

You should be aware that my code puts values into the cell, and Luke's codes puts in a formula. The
formula will return a different result on subsequent days - it wasn't clear whether you wanted that
behavior or not....

HTH,
Bernie
MS Excel MVP

'Macro based on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P"), Target) Is Nothing _
Or Target.Count > 1 Then Exit Sub

'Don't do anything if cell is blank
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

'Create value:
Target.Offset(0, 1).FormulaR1C1 = _
"=IF(RC[-1]="""","""",TODAY()-RC[-1])"

Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Quick VBA Worksheet Change Event or Selection Question:

I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.

In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.

The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.

So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?

If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above) into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.

I thank you for your patience and assistance!

.- Hide quoted text -

- Show quoted text -

Bernie & Luke, I thank you very much for the code! Worked like a
charm!!

 

[Damil4real]

'Macro based on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P"), Target) Is Nothing _
    Or Target.Count > 1 Then Exit Sub

'Don't do anything if cell is blank
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

'Create value:
Target.Offset(0, 1).FormulaR1C1 = _
    "=IF(RC[-1]="""","""",TODAY()-RC[-1])"

Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Quick VBA Worksheet Change Event or Selection Question:

I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.

In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.

The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.

So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?

If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above)  into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.

I thank you for your patience and assistance!

.- Hide quoted text -

- Show quoted text -

These two codes are just great and they both work like a charm!!!!

What if I want to expand this code to include other columns?


How can I expand the worksheet selection code above in order to
incorporate the functions that I have below? I'll greatly appreciate
your assistance.



Data in both column A to F are manually entered.

Data in column J populates when you enter data in column E.

Data in column K populates when you enter data in column F.

Data in columns L, M & O populates when you enter data in column E &
F.

The three Sheet Tabs are named: (Overview, M.A., Vacation Trip).

All of these data are in the Overview tab.

Excel code in each cell of each column are as follows.


Code in column F is:

=IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=TRUE,
0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))

Code in column J is:

=IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=TRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5708,4,FALSE))

Code in column L is:

=J7-K7

Code in column M is:

=IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=TRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A
$2:$C$4573,3,FALSE))=TRUE,0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C
$4573,3,FALSE))

Constant data in column N is 1 (no code)

Code in column O is:

=M7*N7

Data in column P is manually entered (DATE format)

Code in column Q is:

=IF(P7<>"",=TODAY()-P7,"")

The two codes above that I'm trying to expand was initially created to
have column Q populate its code based off of the date entered in
column P…, but now I'm trying to see if I can expand this code to
incorporate the other codes & columns above.

Thanks a great deal for your assistance!!!!!

Thanks!

 

[Bernie Deitrick]

Copy this code, right-Click the "Overview" tab, select view code, and paste
the code into the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myR As Long
Dim myC As Integer

If Target.Cells.Count > 1 Then Exit Sub

myR = Target.Row
myC = Target.Column

Application.EnableEvents = False

If myC = 5 Then 'column E
Range("J" & myR).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))=" _
& "TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5708C5,4,FALSE))"
End If
If myC = 6 Then 'column F
Range("K" & myR).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,2,FALSE))=" _
& "TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,2,FALSE))"
End If
If (myC = 5 Or myC = 6) And (Cells(myR, 5).Value <> "" _
And Cells(myR, 6).Value <> "") Then
Range("L" & myR).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("M" & myR).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))=" _
& "TRUE,0,VLOOKUP(RC7,M.A.!R2C1:R5392C5,5,FALSE))-" _
& "IF(ISNA(VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))" _
& "=TRUE,0,VLOOKUP(RC8,'Vacation Trip'!R2C1:R4573C3,3,FALSE))"
Range("N" & myR).Value = 1
Range("O" & myR).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If

If myC = 16 Then
Range("Q" & myR).FormulaR1C1 = "=IF(RC[-1]<>"""",TODAY()-RC[-1],"""")"
End If
Application.EnableEvents = True
End Sub

'Macro based on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P"), Target) Is Nothing _
Or Target.Count > 1 Then Exit Sub

'Don't do anything if cell is blank
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

'Create value:
Target.Offset(0, 1).FormulaR1C1 = _
"=IF(RC[-1]="""","""",TODAY()-RC[-1])"

Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Quick VBA Worksheet Change Event or Selection Question:

I have a workbook that has a sheet named "Formatted Prices". I enter
data starting from row 7 & from column A to S all the way down on a
daily basis. This range is not completely filled on a daily basis…,
somedays I might just enter 2 or 3 data only filling 3 rows, but
always from column A to S.

In column Q, I have a formula that depends on column P…, it basically
a simple calculation formula that says if the data in say P7 is
empty,
then show nothing, but if the data in P7 is something, then subtract
today's date from P7. The data in P7 is a date and has a date format.
So if I enter today's date in cell P7, Q7 should show 0, If I enter
11/16/2009 in cell P7, Q7 should show 1…etc. down the column.

The problem is that this sheet is used by multiple people and they
often get rid of this simple code. So what happens is I have to come
in and update the code every single time!! I don't want to have to
lock this column because of other issues. As a result, I have decided
that a VBA worksheet change event or selection will be best.

So how can I create a VBA worksheet code that will automatically
populate this formula in column Q as data is enter in column P?

If both columns P & Q are blank, and data are been entered in other
columns, then nothing should happen.
If data is entered in P2, then the worksheet VBA code should auto
populate the code (explained above) into Q2, but nothing in the
other
columns below it…., so they can stay blank.
If data is entered in P3, then the worksheet VBA code should auto
populate the code (explained above) into Q3, but nothing in the other
columns below…, so they can stay blank.
…ect.

I thank you for your patience and assistance!

.- Hide quoted text -

- Show quoted text -

These two codes are just great and they both work like a charm!!!!

What if I want to expand this code to include other columns?


How can I expand the worksheet selection code above in order to
incorporate the functions that I have below? I'll greatly appreciate
your assistance.



Data in both column A to F are manually entered.

Data in column J populates when you enter data in column E.

Data in column K populates when you enter data in column F.

Data in columns L, M & O populates when you enter data in column E &
F.

The three Sheet Tabs are named: (Overview, M.A., Vacation Trip).

All of these data are in the Overview tab.

Excel code in each cell of each column are as follows.


Code in column F is:

=IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=TRUE,
0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))

Code in column J is:

=IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=TRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5708,4,FALSE))

Code in column L is:

=J7-K7

Code in column M is:

=IF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=TRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A
$2:$C$4573,3,FALSE))=TRUE,0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C
$4573,3,FALSE))

Constant data in column N is 1 (no code)

Code in column O is:

=M7*N7

Data in column P is manually entered (DATE format)

Code in column Q is:

=IF(P7<>"",=TODAY()-P7,"")

The two codes above that I'm trying to expand was initially created to
have column Q populate its code based off of the date entered in
column P…, but now I'm trying to see if I can expand this code to
incorporate the other codes & columns above.

Thanks a great deal for your assistance!!!!!

Thanks!