+ Reply to Thread
Results 1 to 3 of 3

Issue loading values using vba to find values on multiple sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Asia
    MS-Off Ver
    O365
    Posts
    11

    Issue loading values using vba to find values on multiple sheets

    The goal is whenever I type a manager name on the textbox; it will search manager on all sheets then load all of the username who reports to the manager on the checkbox caption. The below codes works to find and load values to only one sheets. However sometimes, I have more than 1 sheet (This exclude the main sheet).

    File

    Sheet 2 (Employee):
    username | first | last | manager | role | status
    HAkon | Howard | Akon | Don Kong | User |
    JCorny1 | Jones | Corny | Don Kong | User |
    HMighty | Howard | Mighty | Jack Ben | Manager |

    Sheet 3 (Contractor):
    username | first | last | manager | role | status
    DNygen | Dong | Nygen | Don Kong | User |
    LNygen | Lee | Nygen | Don Kong | User |

    Sheet 4 (Service):
    username | first | last | manager | role | status
    BFinney | Barney | Finney | Don Kong | User |
    CKon | Carillo | Kon | Luz Kurt | Rookie |


    Codes for 1 sheet only
    Call Clearall
    
    Dim gui As Worksheet
    Dim AR As Worksheet
    Dim getfind As String
    Dim qq As Range
    
    Dim i As Long
    
    Set gui = Sheets("GUI")
    Set AR = Sheets("Employee")
    
    For cv = 1 To 40
        cbv(cv) = ""
    Next cv
    
    Set gui = Sheets("GUI")
    Set AR = Sheets("Employee")
    
    'get value from search
    'MsgBox gui.OLEObjects("InputSearch").Object.Value
    getfind = gui.OLEObjects("InputSearch").Object.Value
    'get count result of finding
    rcount = 0
    
    rcount = rcount + Application.WorksheetFunction.CountIf(AR.Range("D:D"), getfind)
    
    'With AR.Range("D:D")
        'Set qq = .Find(what:=getfind, LookIn:=xlValues, Lookat:=xlPart)
    
    
    
        'If Not qq Is Nothing Then
            'firstaddress = qq.Address
    
    
            'Do
                'For x = 1 To rcount
                    'Set qq = .FindNext(qq)
                    'y = qq.Address
    
    
    
    
                    'Caption'
                    'gui.OLEObjects("cb" & x).Object.Caption = x & ". " & AR.Range(y).Offset(0, -2).Value & " " & AR.Range(y).Offset(0, -1).Value & " (" & AR.Range(y).Offset(0, -3).Value & ")"
                    'unhide combobbox
                    'gui.OLEObjects("cb" & x).Visible = True
                    'unhide textbox
                    'gui.OLEObjects("TextBox" & x).Visible = True
    
                'Next x
            'Loop While Not qq Is Nothing And qq.Address <> firstaddress
    
        'End If
    'End With
    What I tried and did not work was I set the 'for n = 1 to sheets.count' above code 'With AR.Range("D:D")', set all 'AR.Range' to 'Sheets(n).Range', then put 'next n' after 'End with'. This only grabs the last person of the sheets it finds then replace it on all checkboxes.
    Last edited by asianqueen; 07-07-2019 at 04:33 PM. Reason: Adding file

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,658

    Re: Issue loading values using vba to find values on multiple sheets

    Try this (not tested).

    Private Sub BSearch_Click()
        Call Clearall
        
        Dim gui       As Worksheet
        Dim AR        As Worksheet
        Dim getfind   As String
        Dim qq        As Range
        Dim x         As Long
        
        'For cv = 1 To 40
        '1    cbv(cv) = ""
        'Next cv
        
        'get value from search
        'MsgBox gui.OLEObjects("InputSearch").Object.Value
        getfind = gui.OLEObjects("InputSearch").Object.Value
        If getfind = "" Then MsgBox "Please enter a name to search for.", vbExclamation, "Invalid Search": Exit Sub
        
        Set gui = Sheets("GUI")
        'Set AR = Sheets("Employee")
        
        For Each AR In Worksheets
            If Not AR Is gui Then
                With AR.Range("D:D")
                    
                    Set qq = .Find(what:=getfind, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
                    
                    If Not qq Is Nothing Then
                        firstAddress = qq.Address
                        Do
                            x = x + 1
                            
                            'Caption'
                            gui.OLEObjects("cb" & x).Object.Caption = x & ". " & qq.Offset(0, -2).Value & " " & qq.Offset(0, -1).Value & " (" & qq.Offset(0, -3).Value & ")"
                            'unhide combobbox
                            gui.OLEObjects("cb" & x).Visible = True
                            'unhide textbox
                            gui.OLEObjects("TextBox" & x).Visible = True
                            
                            Set qq = .FindNext(qq)
                            
                        Loop While qq.Address <> firstAddress
                        
                    End If
                End With
            End If
        Next AR
        
        If x = 0 Then MsgBox "No match found for '" & getfind & "'", vbInformation, "Search Complete"
        
    End Sub
    Last edited by AlphaFrog; 07-08-2019 at 06:50 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    Asia
    MS-Off Ver
    O365
    Posts
    11

    Re: Issue loading values using vba to find values on multiple sheets

    thank you so much. Just need to move set gui = sheets("gui") above getfind.

+ 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. Issue using array to find values across multiple worksheets
    By joshuagill in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-14-2018, 10:01 AM
  2. How to find values in multiple sheets
    By vjharry in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 11:12 AM
  3. Macro for saving / loading values between sheets
    By bell123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 02:41 PM
  4. [SOLVED] Find values from cells in multiple sheets
    By Ragdyer in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 PM
  5. Find values from cells in multiple sheets
    By asubramaniam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  6. Find values from cells in multiple sheets
    By asubramaniam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. Find values from cells in multiple sheets
    By asubramaniam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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