+ Reply to Thread
Results 1 to 3 of 3

Worksheet Change Target Ranges

Hybrid View

  1. #1
    Registered User
    Join Date
    09-11-2015
    Location
    California, usa
    MS-Off Ver
    2010
    Posts
    44

    Worksheet Change Target Ranges

    I have the following worksheet change code. My question to the forum is what is the best practice for identifying target range? The below code runs on a worksheet that has 12K lines and growing. To manage this code most efficiently as it grows and changes, is it best to use named ranges for the target range or simply identifying the ranges (ie rngcheck)? I am open to changing code if necessary. It may be that there is no significant advantage either way. I am still learning VBA and would appreciate any advice. Thanks in advance.
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
       Dim Answer As Long
       Dim Lrow1 As Long
       Dim rngCheck As Range
       Dim CheckCell As Range
       Dim Lrow As Long
       Dim WS1 As Worksheet
       Dim WS2 As Worksheet
       Set WS1 = Sheets("TEST")
       Set WS2 = Sheets("TEST2")
    
        Set rngCheck = Intersect(Target, Range("C3:C5, C7:C1234, C1236:C12000"))
    
        If Not rngCheck Is Nothing Then
            For Each CheckCell In rngCheck.Cells
                If CheckCell.Value > 0 Then
    
                  With WS1.UsedRange
                        Lrow = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
                        WS2.Cells(CheckCell.Row, "C").Copy
                        WS1.Cells(Lrow, "D").PasteSpecial xlPasteValues
                        WS2.Cells(CheckCell.Row, "B").Copy
                        WS1.Cells(Lrow, "K").PasteSpecial xlPasteValues
                        WS2.Cells(CheckCell.Row, "E").Copy
                        WS1.Cells(Lrow, "J").PasteSpecial xlPasteValues
                        WS2.Cells(CheckCell.Row, "D").Copy
                        WS1.Cells(Lrow, "E").PasteSpecial xlPasteValues
                    End With
                End If
            Next CheckCell
        End If
    
        
    
     Set rng1Check = Intersect(Target, Range("C2,C6"))
     If Not rng1Check Is Nothing Then
            For Each Check1Cell In rng1Check.Cells
               If rng1Check.Value > 0 Then
            
     Answer = MsgBox("Add Assembly Kit Yes or No.", vbYesNo)
     Lrow1 = WS1.Cells(WS1.Rows.Count, "D").End(xlUp).Row + 1
     Select Case Answer
      Case vbYes
                        WS2.Cells(Check1Cell.Row, "C").Copy
                        WS1.Cells(Lrow1, "D").PasteSpecial xlPasteValues
                        WS2.Cells(Check1Cell.Row, "B").Copy
                        WS1.Cells(Lrow1, "K").Value = Target.Offset(0, -1).Value & " " & "w/Assembly Kit"
                        WS2.Cells(Check1Cell.Row, "D").Copy
                        WS1.Cells(Lrow1, "E").PasteSpecial xlPasteValues
                        WS1.Cells(Lrow1, "J").Value = Target.Offset(0, 2).Value + 10
               
     Case vbNo
     
                      
                        WS2.Cells(Check1Cell.Row, "C").Copy
                        WS1.Cells(Lrow1, "D").PasteSpecial xlPasteValues
                        WS2.Cells(Check1Cell.Row, "B").Copy
                        WS1.Cells(Lrow1, "K").PasteSpecial xlPasteValues
                        WS2.Cells(Check1Cell.Row, "E").Copy
                        WS1.Cells(Lrow1, "J").PasteSpecial xlPasteValues
                        WS2.Cells(Check1Cell.Row, "D").Copy
                        WS1.Cells(Lrow1, "E").PasteSpecial xlPasteValues
           
     End Select
    
           End If
        Next Check1Cell
     End If
    
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    Last edited by Redled89; 02-03-2016 at 08:48 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,994

    Re: Worksheet Change Target Ranges

    If you use dynamic named ranges, especially for the last one, then the code would be similar to:
     If Not Intersect(Target, Union(Range("test1"), Range("test2"), Range("test3"))) Is Nothing Then
    and at least that part of the code would not have to be edited as the data grows.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-11-2015
    Location
    California, usa
    MS-Off Ver
    2010
    Posts
    44

    Re: Worksheet Change Target Ranges

    Thanks for the timely reply. Appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple Target Intersect Ranges on Same Worksheet
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2013, 04:35 PM
  2. get cell value from worksheet change target
    By vangxbg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 06:07 PM
  3. Worksheet Change: Formula in Target Range
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-29-2010, 02:46 PM
  4. Worksheet Change Target??
    By spinkung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2009, 05:33 AM
  5. [SOLVED] Need to determine the ROW of the TARGET in a Worksheet Change Even
    By Barb Reinhardt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2006, 09:20 AM
  6. Replies: 1
    Last Post: 03-02-2006, 10:40 AM
  7. worksheet change target not recognized
    By Adresmith in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-20-2005, 11:03 AM
  8. Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range)
    By Kevin McCartney in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1