Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Update 2nd sheet when 1st sheet changes...

    Hi guys,

    I am trying to "link" 2 sheets so that if I change the quantity in "Stock", the VBA would change the relevant qty cell in "Trades" as well.
    It is safe to say I am in over my head. This is what I got so far. Any help would be much appreciated.

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    If Target.Cells.Count > 1 Then Exit Sub                                     'ignore multiple selection
    r = Target.Row                                                              'row number of cell that changed
    If r < 3 Then Exit Sub                                                      'ignore changes in top 2 rows
    If r > 47 Then Exit Sub                                                     'ignore changes past row 47
    
    
    '...........................................................................
    If Target.Column = [c1].Column Then                                         'if change is detected in column C..
    
    
    c = Target.Value                                                            'new qty
    d = Target.Offset(0, 3).Value                                               'LID
    e = Target.Offset(0, -1)                                                    'item name
    
    
    Set shtGC = Worksheets("GmailCurrent")                                      'define shortcut
    Set shtSK = Worksheets("Stock")                                             'define shortcut
    Set shtTS = Worksheets("Trades")                                            'define shortcut
    
    
    Application.EnableEvents = False                                            'turn events OFF during changes to sheet
    shtSK.Select                                                                'select Stock
    
    
    'Find the match of "d" in "Stock"..
    For i = 2 To 5000 Step 24                                                   'start in row 2; then 26;50;74; +24 etc etc
        For j = 2 To 28 Step 7                                                  'check trade number cell in row; every 7 columns across
            If Cells(i, j) = c Then Exit Sub                                    'exit when the cell with the same value is found
        Next j                                                                  'move across columns..
    Next i                                                                      '..then down to next block
    
    
    'Find the match of "e" in "Range(Target.Offset(2), Target.Offset(6))"..
    
    
    'Make qty of the item c
    PreviousResult.Offset(0, 1) = c
    
    
    Application.EnableEvents = True                                             'make sure event trapping is ON
    
    
    End If
    
    
    End Sub
    Trades 3.6.xlsm

    Thanks for looking...
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-16 at 15:34.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Ferenc,

    This amended code will track changes in QTY in col C on the Stock sheet. If a change is made, it will find the items LID number on the Trade sheet and enter the QTY value in the QTY column.

    HTH,
    Maud

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("C3:C47")) Is Nothing Then
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim shtGC As Worksheet, shtSK As Worksheet, shtTS As Worksheet
        Dim I As Long, J As Long
        Set shtGC = Worksheets("GmailCurrent")
        Set shtSK = Worksheets("Stock")
        Set shtTS = Worksheets("Trades")
    '--------------------------------------------
    'FIND ITEM USING LID AND UPDATE QTY
        With shtTS
        Application.EnableEvents = False
        For I = 2 To 5000 Step 24
            For J = 2 To 23 Step 7
                If .Cells(I, J) = Target.Offset(0, 3) Then
                    .Cells(I, J).Offset(2, 2) = Target
                End If
            Next J
        Next I
        Application.EnableEvents = True
        End With
    '--------------------------------------------
    'CLEANUP
    Set shtGC = Nothing
    Set shtSK = Nothing
    Set shtTSC = Nothing
    End If
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Ferenc Nagy (2015-07-16)

  4. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you Maudibe,

    It was pretty close, but the code kept changing the quantity in the same row every time until I changed the middle of it from this:

    Code:
    '--------------------------------------------
    'FIND ITEM USING LID AND UPDATE QTY
        With shtTS
        Application.EnableEvents = False
        For I = 2 To 5000 Step 24
            For J = 2 To 23 Step 7
                If .Cells(I, J) = Target.Offset(0, 3) Then
                    .Cells(I, J).Offset(2, 2) = Target
                End If
            Next J
        Next I
        Application.EnableEvents = True
        End With
    '--------------------------------------------
    To this:

    Code:
    '--------------------------------------------'FIND ITEM USING LID AND UPDATE QTY
        With shtTS
        Application.EnableEvents = False
        For I = 2 To 5000 Step 24
            For J = 2 To 23 Step 7
                If .Cells(I, J) = Target.Offset(0, 3) Then
                If .Cells(I, J).Offset(2) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(2, 2) = Target
                End If
                If .Cells(I, J).Offset(3) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(3, 2) = Target
                End If
                If .Cells(I, J).Offset(4) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(4, 2) = Target
                End If
                If .Cells(I, J).Offset(5) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(5, 2) = Target
                End If
                If .Cells(I, J).Offset(6) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(6, 2) = Target
                End If
                End If
            Next J
        Next I
        Application.EnableEvents = True
        End With
    '--------------------------------------------
    I have 2 follow up questions:

    - Shouldn't there be a "Exit Sub" after the quantity was found and changed, so it stops, or am I wrong about that?

    - Curious, is there a more efficient way to write the part I highlighted with bold? Feels like it should be something along the lines of:

    Code:
    cRow = 2
    
    Range(cells(I, J).Offset(2), cells(I, J).Offset(6))
    
    If cells(I, J).Offset(cRow) = Target.Offset(0, -1) Then 
    Cells(I, J).Offset(cRow, 2) = Target
    Exit Sub
    End If
    cRow = cRow + 1
    
    End
    but I think I am missing something...

    Thanks
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-16 at 21:14.

  5. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    After some experimenting I figured it out...

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)If Not Intersect(Target, Range("C3:C47")) Is Nothing Then
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim shtTS As Worksheet
        Dim I As Long, J As Long, K As Long
        Set shtTS = Worksheets("Trades")
    '--------------------------------------------
    'FIND ITEM USING LID AND UPDATE QTY
        With shtTS
        Application.EnableEvents = False
        For I = 2 To 5000 Step 24
            For J = 2 To 23 Step 7
                If .Cells(I, J) = Target.Offset(0, 3) Then
                For K = 2 To 6 Step 1
                    If .Cells(I, J).Offset(K) = Target.Offset(0, -1) Then
                    .Cells(I, J).Offset(K, 2) = Target
                    Application.EnableEvents = True
                    Exit Sub
                    End If
                Next K
                End If
            Next J
        Next I
        Application.EnableEvents = True
        End With
    '--------------------------------------------
    'CLEANUP
    Set shtTS = Nothing
    End If
    End Sub

Posting Permissions

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