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

    Post Result extraction back to "Sub worksheet_change"

    Hi guys,

    I have the following code that I could use several times in my excel file.
    How would I "extract" the location back into the worksheet_change sub?

    Code:
    Sub findLID(LID)
    
    
        Dim shtTrades As Worksheet
        Dim I As Long
        Dim J As Long
        Set shtTrades = Worksheets("Trades")
    
        J = 23    zRow = LID * 6 - 22
    '.......................................................................
    With shtTrades                                                                  'Update Trade
        For I = zRow To I + 18 Step 6
            If zRow < 0 Then
                zRow = zRow + 6
            End If
            If .Cells(I, J) = LID Then
    
    Extract "I" & "J" back to "Sub worksheet_change"...
    
            End If
            J = J - 7
        Next I
    End With
    '.......................................................................
    End Sub
    Thanks for looking...
    Ferenc

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Set I to a public variable num1 and J to a public variable num2. Both can be used in your Worksheet_Change event routine.

    HTH,
    Maud

    Code:
    Public num1 as Long, num2 as Long
    
    Public Sub Test()
    Dim shtTrades As Worksheet
        Dim I As Long
        Dim J As Long
        Set shtTrades = Worksheets("Trades")
    
        J = 23    zRow = LID * 6 - 22
    '.......................................................................
    With shtTrades                                                                  'Update Trade
        For I = zRow To I + 18 Step 6
            If zRow < 0 Then
                zRow = zRow + 6
            End If
            If .Cells(I, J) = LID Then
                num1 = I
                num2 = J
            End If
            J = J - 7
        Next I
    End With
    '.......................................................................
    End Sub

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

    Ferenc Nagy (2015-07-23)

Posting Permissions

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