0

I have made an macro through which i extract data from 5 files and consolidate all that information in one sheet known as "SEARCH". giving input as member ID all of the get fetched from the 5 files and get consolidated on the main "SEARCH" file. but now i want to search the files through member name and member DOB as input using user form. i am new to user form concept. please help on the coding part.

the problem while having DOB as input in user form does not need any combo box as the user just need to copy data from the 5 files having DOB having the format same as those in 5 files data. so exact same match required. 

Sub SearchSheets_MemberID()
    Dim FirstAddress As String, WhatFor As String
    Dim Cell As Range, Sheet As Worksheet
    Dim lr As Long

    Sheets("SEARCH").Range("A5:Z65000").ClearContents
    WhatFor = InputBox("Please Enter Member ID", "Search Criteria")

    If WhatFor = Empty Then
        Call Module4.SearchSheets_MemberName
    End If

    For Each Sheet In Sheets
        If Sheet.Name <> "SEARCH" Then
            'filter and sort for newest to oldest date
            Sheet.Activate
            ActiveWorkbook.ActiveSheet.AutoFilterMode = False
            Rows("3:3").Select
            Selection.AutoFilter          
            Range("A4:Z100000").Sort key1:=Range("B3"),order1:=xlDescending,Header:=xlNo

            With Sheet.Columns(6)
                Set Cell = .find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole)

                If Not Cell Is Nothing Then
                    FirstAddress = Cell.Address

                    Do
                        Cell.EntireRow.Copy _
                            Destination:=Sheets("SEARCH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                    Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
                End If
            End With
        End If


        End With
    Next Sheet

    Set Cell = Nothing
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
kunal goswami
  • 1
  • 1
    This code cannot run because there is an additional `End With` right before `Next Sheet`. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jul 25 '19 at 07:24
  • thank you peh, but i want to use user form having input as member name and dob as input,how to code these inputs to link it as above code link member id as input to other 5 files.if you can guide me over that that will be a great help. – kunal goswami Jul 26 '19 at 04:43

0 Answers0