VBA Worksheet Event Change or Selection Question, please assist me

D

Damil4real

Two fantastic people were kind enough to provide me these two codes
below, and now I'm trying to see if I can expand it to include other
columns and data in a worksheet.

Code 1:

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


Code 2:

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P: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

-------------------------------------------------

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!
 
B

Bernie Deitrick

Go back to the thread in Worksheet.functions and see my reply there.

Bernie


Two fantastic people were kind enough to provide me these two codes
below, and now I'm trying to see if I can expand it to include other
columns and data in a worksheet.

Code 1:

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


Code 2:

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P: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

-------------------------------------------------

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!
 
M

muddan madhu

Try this , Based on Column P, macro executes

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range
Dim Shtn1 As Range, shtn2 As Range
Dim Value1 As Integer, value2 As Integer, Rn As Integer


Set Shtn1 = Sheets("Vacation Trip").Range("A2:C4573")
Set shtn2 = Sheets("M.A.").Range("A2:E5708")

If Intersect(Target, Range("P:p")) Is Nothing Then Exit Sub
Application.EnableEvents = False

For Each myC In Intersect(Target, Range("P:p"))
Rn = myC.Row
Value1 = Range("H" & Rn)
value2 = Range("G" & Rn)

With Application.WorksheetFunction
On Error Resume Next
Qres1 = .VLookup(Value1, Shtn1, 2, 0)
If IsError(Qres1) Then Qres1 = 0

Qres2 = .VLookup(value2, shtn2, 4, 0)
If IsError(Qres2) Then Qres2 = 0

Qres3 = .VLookup(value2, shtn2, 5, 0)
If IsError(Qres3) Then Qres3 = 0

Qes4 = .VLookup(Value1, Shtn1, 5, 0)
If IsError(Qres4) Then Qres4 = 0
End With

Range("F" & Rn) = Qres1
Range("J" & Rn) = Qres2
Range("L" & Rn) = Range("J" & Rn) - Range("K" & Rn)
Range("M" & Rn) = Qres3 - Qres4
Range("N" & Rn) = 1
Range("O" & Rn) = Range("M" & Rn) * Range("N" & Rn)
Range("Q" & Rn).Value = Date - Target.Value

Next myC

Application.EnableEvents = True

End Sub


Two fantastic people were kind enough to provide me these two codes
below, and now I'm trying to see if I can expand it to include other
columns and data in a worksheet.

Code 1:

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

Code 2:

Private Sub Worksheet_Change(ByVal Target As Range)
'Don't do anything unless needed
If Intersect(Range("P: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

-------------------------------------------------

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!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why are the two codes below not getting the right formula for myworksheet event change formula? 2
The code below worked for a few days and just totally stoppedworking. What can I do to make sure it 1
Event recognition. 11
Pass condition formatting across worksheets via vba. 0
Can someone please tell me why my code stopped working??? 1
loop through rows 2
Quick VBA Worksheet Change Event or Selection Question: 1
Ambiguous name detected: worksheet_change 2

Top