VBA Worksheet Event Change or Selection Question, please assist me

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 =3D False
For Each myC In Intersect(Target, Range("P:P"))
Range("Q" & myC.Row).Value =3D Date - Target.Value
Next myC
Application.EnableEvents =3D 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 =3D "" Then Exit Sub


Application.EnableEvents =3D False


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


Application.EnableEvents =3D 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:


=3DIF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=3DTRUE,
0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))


Code in column J is:


=3DIF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=3DTRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5708,4,FALSE))


Code in column L is:


=3DJ7-K7


Code in column M is:


=3DIF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=3DTRUE,0,VLOOKUP
($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!
$A
$2:$C$4573,3,FALSE))=3DTRUE,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:


=3DM7*N7


Data in column P is manually entered (DATE format)


Code in column Q is:


=3DIF(P7<>"",=3DTODAY()-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=85, 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!
0
Damil4real
11/17/2009 8:39:54 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1831 Views

Similar Articles

[PageSpeed] 29

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

Bernie


"Damil4real" <damil4real@gmail.com> wrote in message 
news:31bc5919-5af8-499f-8944-7ffa07b844a9@x15g2000vbr.googlegroups.com...
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! 

0
Bernie
11/17/2009 10:29:25 PM
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 =3D Sheets("Vacation Trip").Range("A2:C4573")
Set shtn2 =3D Sheets("M.A.").Range("A2:E5708")

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

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

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

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

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

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

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

Next myC

Application.EnableEvents =3D True

End Sub


On Nov 18, 1:39=A0am, Damil4real <damil4r...@gmail.com> wrote:
> 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 =3D False
> For Each myC In Intersect(Target, Range("P:P"))
> Range("Q" & myC.Row).Value =3D Date - Target.Value
> Next myC
> Application.EnableEvents =3D 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 _
> =A0 =A0 Or Target.Count > 1 Then Exit Sub
>
> 'Don't do anything if cell is blank
> If Target.Value =3D "" Then Exit Sub
>
> Application.EnableEvents =3D False
>
> 'Create value:
> Target.Offset(0, 1).FormulaR1C1 =3D _
> =A0 =A0 "=3DIF(RC[-1]=3D"""","""",TODAY()-RC[-1])"
>
> Application.EnableEvents =3D 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:
>
> =3DIF(ISNA(VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))=3DTRUE,
> 0,VLOOKUP($H7,'Vacation Trip'!$A$2:$C$4573,2,FALSE))
>
> Code in column J is:
>
> =3DIF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5708,4,FALSE))=3DTRUE,0,VLOOKUP
> ($G7,M.A.!$A$2:$E$5708,4,FALSE))
>
> Code in column L is:
>
> =3DJ7-K7
>
> Code in column M is:
>
> =3DIF(ISNA(VLOOKUP($G7,M.A.!$A$2:$E$5392,5,FALSE))=3DTRUE,0,VLOOKUP
> ($G7,M.A.!$A$2:$E$5392,5,FALSE))-IF(ISNA(VLOOKUP($H7,'Vacation Trip'!
> $A
> $2:$C$4573,3,FALSE))=3DTRUE,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:
>
> =3DM7*N7
>
> Data in column P is manually entered (DATE format)
>
> Code in column Q is:
>
> =3DIF(P7<>"",=3DTODAY()-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=85, 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!

0
muddan
11/17/2009 10:31:30 PM
Reply:

Similar Artilces:

Change entries in popup list for partially typed email addresses
When I start a new email and type in the first few letters of a recipient name or email address, I am given a helpful list of candidate addresses. However, the email addresses of one acquaintance in this list needs to be corrected. How can I do this? I tried looking in the address book, but none of the lists seem to contain this contact. I am using Outlook 2003 on Windows XP. The autocompletion cache has nothing to do with your Contacts. It is simply a cache of your prior recipients. Remove individual addresses from the autocompletion cache by highlighting the entry when pres...

A CString question #2
Hi to all, Given the following: CString sTheString = "Hello world"; void UseTheString(CString s) { ... } Where UseTheString() is a function that takes a string as an argument. My questions are the following: 1. Is it rather better to use the below version of the function? If yes, why? void UseTheString(LPCTSTR s) { ... } 2. When can I substitute a CString with a LPCTSTR and vice versa? Thanks a lot, Geo Geo wrote: > Hi to all, > > Given the following: > > CString sTheString = "Hello world"; > void UseTheString(CString s) &...

installed office 2007, 2003 calendar events did not appear.
I had a error 1311 on installation of office 2007. The error solution was to uninstall office 2003 and reinstall 2007. Everything came up fine except calendar. None of my events from 2003 showed up, however my reminder popups worked from the 2003 calendar. I can't find the name of the calendar file to try to reinstall. Thanks, Charlie The calendar is in your pst. Do you see the calendar if you use Folder list view? (Press Ctrl+6 to switch to the folder list. ) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions...

[Q] CButton events being triggered automatically
Hiya I've been trying to work around an annoying problem without much luck... I have an MDI app with a floating settings dialog. Each time the active child window changes, the floating dialog updates its widgets according to that child's attributes. One particular attribute is represented as a pair of 'push like' radio buttons. The problem is that when I change these using SetCheck(BST_[UN]CHECKED), the button click event handlers get called whenever the dialog becomes active. It doesn't seem to make any difference whether the buttons are set to 'auto'...

changing tcp port used by exchange 5.5?
Hello, We are trying to find out if it is possible to change the tcp port that exchange 5.5 uses to send outgoing (internet bound) emails? specifically, we are trying to have our internal exchange 5.5 server send all outbound emails to our unix based/postfix server using a port other then 25. I have search both technet and google but seem to only find articles relating to inbound or client based issues. can you point me in the right direction/tech article? I have to admit I don't know if this applies to 5.5, but maybe it'll help if it's somewhat similar. This is how it'...

Report Footer Help Needed Please
I have a report (a bill of lading) that I want to print 4 copies of. But at the bottom of each page I need a different statement printed (1st copy - "Original", 2nd copy "Customer Copy", 3rd copy "File Copy", etc.) Can anyone guide me in the right direction to accomplish this? Duane: Thanks for your quick response. I tried this, but I can not get it to work. Any other suggestions? Thanks, T "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message news:988241CB-1FD8-4EA7-98AD-7310A005CAA2@microsoft.com... > Allen Browne has a ...

Updating "Master" Excel Worksheet
I'm in a national law enforcement association with over 10k members. We keep our national membership records in Access. Replication would be wonderful tool, but not a lot of the chapters have Access, but most of the members have Excel. We would like to send each chapter their membership info in an Excel worksheet for them to update and then return to us to update our national records without changing each entry by hand. I'm not sure if this is an Access or Excel question, or maybe both, but is there a way to do this using the automated features? I'll be eternally grateful...

how to change range for dynamic chart in excel 2000 with button?
i need a help please. i have problem in excel chart. i add data to the table. and then if i want to update my chart i need to change the range too, isn't it? is there any solution for that? i've already use dynamic chart. but it always show data from Jan04 till Jan05. If I add Feb05 it will change from Feb04 to Feb05. what about if i want to see Dec03 till Dec04? is there any command or something else like using macro or vb? thank you in advance.. ivan Ivan, You should be able to set the dynamic chart to show just about any range you want. These articles should help: http://pu...

Changing English text to Spanish text
Hello can anyone help me out with this... I'm creating a newsletter, and want to see if there is a way to convert the english text to spanish... Any help would be great.. Thank you Gilbert Saenz sagi300@dshs.wa.gov There are translation services available for a cost. "Gilbert Saenz" <anonymous@discussions.microsoft.com> wrote in message news:184001c3fbd5$f566d9b0$a001280a@phx.gbl... > Hello can anyone help me out with this... I'm creating a > newsletter, and want to see if there is a way to convert > the english text to spanish... Any help would be great.....

select all for check box..
HI.. I have a check box field in a continuous form... name is mCheck I want to have a command button Select All such that when clicked, all check boxes in the continuous form would be selected...Thank you.. -- ai® Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1 A continuous form would display a set of records. Are you saying that you want all the records in the set marked with a check-mark? If so, one way to do this would be to run a query via code behind the command button that updates the field underlying the checkbox in a...

Excell
How do I change font colour in a footer?? There are options to change the font, size, underline etc but not colour... Can anyone help? You can't change the font colour in a footer. If you really need coloured text, you could print just the footer using Word, then print the Excel file onto those pages. Or, if you have Excel 2002, you can insert a coloured picture into the footer, so you could create a graphic with the text, and insert that. cinabar wrote: > How do I change font colour in a footer?? > > There are options to change the font, size, underline etc > but not...

Money for ppc question ?
I don't see how to record buy/sells of a stock.Only my checking account allows me to input data.I s there a way to configure this ? Thanks ...

Lead conversion question
Is there a way to convert a lead (which has an individual name along with a company name) into an Account and convert the individual name into a contact in one step? On Aug 10, 10:58 am, Russell Brandford <RussellBrandf...@discussions.microsoft.com> wrote: > Is there a way to convert a lead (which has an individual name along with a > company name) into an Account and convert the individual name into a contact > in one step? I assume by one step you just want to have a button that will create both an Account and Contact entity with one click, correct? You'll have to writ...

hide colum based on cell value on each worksheets
I have a workbook with several worksheets. I am trying to write a VBA macro that would look at row 5 to see if any cells indicate "Hide column". If it does, it would hide all the columns indicating "Hide column" on this worksheet. Then it would continue the same process to the next worksheet, and the next one ... I currently have the macro working for the individual worksheet (see below), but I would like to have it work to do all the worksheets of the workbook. Sub HideColumn() Application.ScreenUpdating = False Application.Calculation = xlCalculation...

ActiveX question #3
Hello, I have a dialog box acting as an ActiveX control written in MFC in Internet Explorer. In this dialog box I have a dialog frame with the minimize and close boxes. I catch the close method using the WM_CLOSE message. Everything works fine, except the ActiveX does not quit, when I press the close button on the dialog frame and my application keeps running, which is bad. IS there a way to shut down an ActiveX control without shutting down Internet Explorer? Thanks, Yury ...

column/block/rectangular selection of text in Word 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel What happened to the ability to select a column/block/rectangular selection of text by holding down the alt/option key in Word 2008? How do I make a vertical text selection? <br> Thanks in advance. <br> JBM "Nothing" and "The Same Way" :-) However various things may get in the way: Spaces is one, having the Toolbox open is another. Anything that distracts the focus from the document page can potentially interfere with the operation of this function. As usual, the ...

Excel VBA
I have been able to get a button that inserts rows but also need th formula in the new row to copy from the row moved. Below is my code for inserting rows. What would I add to have th formulas across the row to copy to the new row?? Sub Macro4() Range("TotalRev").Select ActiveCell.Offset(-1, 0).Select Selection.EntireRow.Insert Range("COGS").Select ActiveCell.Offset(-1, 0).Select Selection.EntireRow.Insert End Sub Thank -- Message posted from http://www.ExcelForum.com David McRitchie has some code you can steal at: http://www.mvps.org/dmcritchie/excel/insrtrow.htm &...

When message changes from text to HTML format, font is not the default
Hi, Environment: My outlook 2003 default format is HTML. The Default fond for new/reply messages is Arial. The Problem: I get a message in text format. When I manually change the format of this message to HTML, the font of the message is Times Roman. How can I make outlook automatically use the default font (Arial) as the font for messages changed from text to HTML ? Thanks, Ron. -- ronbarak ...

Textbox value automatically selected, How?
This has to be a simple one. When opening a userform I would like the value stored in the firs textbox to be selected. Therefore I can just start typing over th current value. Currently I have to click and drag the mouse over th old value to select it -- Message posted from http://www.ExcelForum.com Kevlar Try Private Sub UserForm_Activate() Me.TextBox1.SelStart = 0 Me.TextBox1.SelLength = Len(Me.TextBox1) End Su -- Message posted from http://www.ExcelForum.com That works well. Thanks Now, how do I get the list of choices in a listbox to default to th top ite -- Message posted from...

Publisher Question #5
How can you select the toolbars that appear on your workspace Isbell wrote: > How can you select the toolbars > that appear on your workspace ============================ Not sure what you mean by "select". If you're asking how to toggle them on or off...just right click and check or uncheck the one you're interested in. Also you can access the settings at... View / Toolbars. -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a voluntee...

How to not have formula change after inserting column from another sheet
One file - a few sheets. I run a macro weekly to copy column B of my CURRENT PORTFOLIO sheet and insert that data between Col I and J (Moving older data to the right - off the screen.) I then populate col B with this weeks data. That works fine, BUT another sheet of that file ( GRAPH) uses that data to track a few moving averages. To keep the GRAPH sheet accurate, after the macro I then have to modify the contents for the SOURCE field of the graph because NOW $I$1 becomes $J$1 and $I$28 becomes $J$28. I can't use an INDIRECT command - I get an error. Name = Weekly Moving Average, ...

Worksheet Column Widths Different For Different Users
There are 2 of us that use the same Excel Workbook. We are all using Excel 2003. When I check the widths of the columns in the Worksheet, they are different then when the others check them. For example, for me, one column is set at a width of 10.38. That same column, shows a width of 10.43 for the other user. When I try to change the width of that column to 10.43, it stays at 10.38 (for me). I can't even change it to 10.43. When the other user tries to change that column to 10.38, it stays at 10.43 (for that user). So, that user can't even change it to 10.38. ...

TEST
TEst In article <C06942D8.6C52%zeugma@toucanspam.com>, Jason O <zeugma@toucanspam.com> wrote: > TEst There's a testing newsgroup that uses the same server. Please use that: microsoft.public.test.here ...

excell worksheet
I need to coordinate information from one excell worksheet into another one. How do I make that happen? What do you mean with "coordinate"? Have you try a VLOOKUP? -- Yanick "dontblinkmax" wrote: > I need to coordinate information from one excell worksheet into another one. > How do I make that happen? I have not. What I need to do is be able to enter information into one worksheet and have it appear in another. To allow me to show data to certain people or look at all the data on one page. "Yanick" wrote: > What do you mean with "coor...

Automatically selecting active chart
I have the following marco below. I would like to change it so that I do not have to select an active chart before running it. Instead, I would like it to always run for the chart in sheet titled "Benchmark Comp Chart". Please note, there is never more than one chart in that sheet. Currently, since I have to select an active chart, I have to run this while my active sheet is "Benchmark Comp Chart". Going forward, I wish to run it while my active sheet is titled "Misc.". Sub GetChartValues() Dim NumberOfRows As Integer Dim X As Object ...