Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Activating second part of macro

    I would like to activate the second part of my macro (in blue) after searching for the ref # and amending the dates i.e. call the second part of the macro after the entire search is complete

    Your assistance in this regard is most appreciated


    Code:
     Sub Public Sub FindAndSelect()
        
        Sheets(4).Select
        Dim searchRng As Range: Set searchRng = Range("A:A")
        
        Dim key As Variant: key = Application.InputBox("Please Enter Ref to search for")
        Dim found As Range: Set found = searchRng.Find(key, LookIn:=xlValues)
        
        If found Is Nothing Then
            MsgBox ("Ref: " & key & " not found")
            Exit Sub
        End If
        
        Range("AB" & found.Row).Select
     Sheets("Old Data").Select
        Range("A:BD").ClearContents
        Sheets("Data").Select
        Range("A:BD").Copy
        Sheets("Old Data").Select
        Range("A1").PasteSpecial Paste:=xlPasteFormulas
        Range("a1").Select
        Application.CutCopyMode = False
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    I think what you are after is:
    Code:
     Sub Public Sub FindAndSelect()
        
        Sheets(4).Select
        Dim searchRng As Range: Set searchRng = Range("A:A")
        
        Dim key As Variant: key = Application.InputBox("Please Enter Ref to search for")
        Dim found As Range: Set found = searchRng.Find(key, LookIn:=xlValues)
        
        If found Is Nothing Then
            MsgBox ("Ref: " & key & " not found")
        Else
          Range("AB" & found.Row).Select
          Sheets("Old Data").Select
          Range("A:BD").ClearContents
          Sheets("Data").Select
          Range("A:BD").Copy
          Sheets("Old Data").Select
          Range("A1").PasteSpecial Paste:=xlPasteFormulas
          Range("a1").Select
          Application.CutCopyMode = False
       End If
     
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    What I would like to do is to search for all the ref numbers and once they have all been selected , then the second part of the code can be activated.

    I am unsure of how to tackle this

    It would be appreciated if you could assist

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Ok I missed that. What you need to do is DIM an array to hold your list of found keys. Hopefully, there won't be too many so your array doesn't have to be too big. You need to DIM it for the maximum number (you could use REDIM to resize it if necessary but that's very inefficient).

    Next you put your INPUTBOX statement in a loop, exiting when it is blank. You'll also need to keep count of the number of keys actually found. This would be done in the Else clause of the If testing if the search value was found.

    Once you exit the loop you then just continue with the rest of the code.

    Sample Untested AIR code:
    Code:
     Sub Public Sub FindAndSelect()
        
        Dim vKey                 As Variant
        Dim rngFound             As Range
        Dim rngSearchRng         As Range
        Dim iKeyCnt              As Integer
        Dim lRowsToProcess[1:10] As Long
    
        iKeyCnt = 0
        Sheets(4).Select
        Set searchRng = Range("A:A")
        
        Do
          vKey = Application.InputBox("Please Enter Ref to search for")
          Set rngFound = searchRng.Find(key, LookIn:=xlValues)
        
          If rngFound Is Nothing Then
            MsgBox ("Ref: " & key & " not found")
          Else
            iKeyCnt = iKeyCnt + 1
            lRowsToProcess[iKeyCnt] = rngFound.row
          End if
    
        Loop Until vKey = ""   'Exit when no input
    
        For iCntr = 1 to iKeyCnt
          Range("AB" & lRowsToProcess(iCntr)).Select
          Sheets("Old Data").Select
          Range("A:BD").ClearContents
          Sheets("Data").Select
          Range("A:BD").Copy
          Sheets("Old Data").Select
          Range("A1").PasteSpecial Paste:=xlPasteFormulas
          Range("a1").Select
          Application.CutCopyMode = False
        Next iCntr
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the code and explanation. There may be a max of 10 items to be searched.

    The following code comes up with a compile error: Syntax error. Not sure how to correct this

    It would be appreciated if you would correct this.

    Code:
    Dim lRowsToProcess[1:10] As Long
    I tried , but also comes up with compile error


    Code:
    Dim lRowsToProcess (1:10) As Long

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try changing to:

    Dim lRowsToProcess(10) As Long.

    You will also need to change the brackets to parentheses:
    lRowsToProcess(iKeyCnt) = rngFound.Row

    Change 2 instances of "key" to "vkey"
    Last edited by Maudibe; 2013-12-15 at 08:51.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Sorry I was a bit sleepy and as stated I didn't test the code.

    Thanks Maud for finding the missed changes.

    Here's code that at least compiles correctly.
    Code:
    Option Explicit
    
    Public Sub FindAndSelect()
        
        Dim vKey                    As Variant
        Dim rngFound                As Range
        Dim rngSearchRng            As Range
        Dim iKeyCnt                 As Integer
        Dim lRowsToProcess(1 To 10) As Long
        Dim iCntr                   As Integer
    
        iKeyCnt = 0
        Sheets(4).Select
        Set rngSearchRng = Range("A:A")
        
        Do
          vKey = Application.InputBox("Please Enter Ref to search for")
          Set rngFound = rngSearchRng.Find(vKey, LookIn:=xlValues)
        
          If rngFound Is Nothing Then
            MsgBox ("Ref: " & vKey & " not found")
          Else
            iKeyCnt = iKeyCnt + 1
            lRowsToProcess(iKeyCnt) = rngFound.Row
          End If
    
        Loop Until vKey = ""   'Exit when no input
    
        For iCntr = 1 To iKeyCnt
          Range("AB" & lRowsToProcess(iCntr)).Select
          Sheets("Old Data").Select
          Range("A:BD").ClearContents
          Sheets("Data").Select
          Range("A:BD").Copy
          Sheets("Old Data").Select
          Range("A1").PasteSpecial Paste:=xlPasteFormulas
          Range("a1").Select
          Application.CutCopyMode = False
        Next iCntr
    
    End Sub
    Note: Using Dim lRowsToProcess(1 to 10) creates a 1 based array vs a zero based array with Dim lRowsToProcess(10) either will work but then you have to adjust the counter and For loop. Just different approaches both will work if coded properly.

    HTH

    BTW: Sample files are useful as they allow testing before posting solutions.
    Last edited by RetiredGeek; 2013-12-15 at 09:28.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG & Maud

    Thanks for the input. RG your code run perfectly now. I just need one small change. Once the Ref # has been selected, the user must be able to edit the date in Col AB , before doing the next search

    I have attached some sample data

    It will be appreciated if you would amend your code to accommodate this

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-12-15 at 10:09. Reason: Attaching Sample Data

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Why don't you give it a try first? You just need to get the input from the user (as you do for the Ref *#) and then write it to the appropriate cell. This would be done in the Else branch of the IF code. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    See code below, where I have made the changes. Once I search for the Ref #, it takes me to the appropriate col and I enter the date, but the date does not change

    I have highlighted my changes in blue. Kindly test the code and amend

    Code:
     Option Explicit
    
    Public Sub FindAndSelect()
        
        Dim vKey                    As Variant
        Dim rngFound                As Range
        Dim rngSearchRng            As Range
        Dim iKeyCnt                 As Integer
        Dim lRowsToProcess(1 To 10) As Long
        Dim iCntr                   As Integer
    
        iKeyCnt = 0
        Sheets(4).Select
        Set rngSearchRng = Range("A:A")
        
        Do
          vKey = Application.InputBox("Please Enter Ref to search for")
          Set rngFound = rngSearchRng.Find(vKey, LookIn:=xlValues)
        
          If rngFound Is Nothing Then
            MsgBox ("Ref: " & vKey & " not found")
          Else
            iKeyCnt = iKeyCnt + 1
            lRowsToProcess(iKeyCnt) = rngFound.Row
             lRowsToProcess(iKeyCnt) = rngFound.Offset(, 54)
              vKey = Application.InputBox("Please enter the date demo transferred to UV stock")
          End If
    
        Loop Until vKey = ""   'Exit when no input
    
        For iCntr = 1 To iKeyCnt
          Range("AB" & lRowsToProcess(iCntr)).Select
          Sheets("Old Data").Select
          Range("A:BD").ClearContents
          Sheets("Data").Select
          Range("A:BD").Copy
          Sheets("Old Data").Select
          Range("A1").PasteSpecial Paste:=xlPasteFormulas
          Range("a1").Select
          Application.CutCopyMode = False
        Next iCntr
    
    End Sub

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    You need to assign it to the cell:
    Code:
              cells(rngFound.row,28).value = Application.InputBox("Please enter the date demo transferred to UV stock")
    Note: the 28 above = Column AB as stated in post #8.
    Don't know what you were doing with the line before that in blue but I don't think you need it.
    HTH

    BTW: To find a column number vs letters use: ?[celladdress].Address(,,xlR1C1)
    in the Excel VBE Immediate window.
    findcolnumber.JPG
    Last edited by RetiredGeek; 2013-12-15 at 12:11.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the help and explanations, much appreciated. Code works perfectly

    Howard

Posting Permissions

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