VBA Nested If / range match

Question

I have a pretty large excel file that houses a list of employees, a few columns of paycheck data, then a fiscal week assigned to when that data was collected.

I am trying to search though this data and match an employee with a specific fiscal week in a macro. I have a solution that finds the name, but wont print out the fiscal week and it is very slow and I'm sure that there are much better ways of doing this simple task. Below is what I have, it's pretty simple and in the end I will need to capture the data in the rows but for now I am just printing to have proof of concept.

Sub loop_test()
    Dim ClientTable As Range
    Dim rng1 As Range, rng2 As Range, desired_emp As String, desired_fw As Integer

    desired_emp = Application.InputBox("Select an Employee", Type:=8)
    desired_fw = Application.InputBox("What FW would you like to do this for?", Type:=8)


    Set FullName = Sheets("Query5").Range("A:A")
    Set FiscalWeek = Sheets("Query5").Range("F:F")

    For Each rng1 In FullName.Columns(1).Cells
        If rng1.Value = desired_emp Then
            matched_name = rng1.Cells.Value

            For Each rng2 In FullName.Columns(1).Cells
                If rng2.Value = desired_fw Then
                    matched_fw = rng2.Cells.Value
                End If
            Next
        End If
    Next

    Range("i3").Value = matched_name
    Range("j3").Value = matched_fw

End Sub

Try autofiltering instead of looping. Here is an example that should get you started: http://stackoverflow.com/a/16901714/138938. You can set the criteria for the autofilter to the employee and fiscal week. — Jon Crowell, Jun 19 '13 at 20:03
Both your outer and inner loop are searching the first column of data. Inner loop should be `For Each rng2 In FiscalWeek.Cells` However this just finds the first match in either column: presumably they should both be on the same line? — Tim Williams, Jun 19 '13 at 20:22
Does it matter that the variables "matched_name" and "matched_fw" are not DIMed? — Dale, Jun 19 '13 at 20:36
You may not need a macro to do this, have you tried using 2 input cells within excel and use the `VLookup()` function. — chancea, Jun 19 '13 at 21:43

Jon Crowell · Accepted Answer · 2013-06-19T20:44:45.200

I set up an example range with names and fiscal weeks in columns A and B. Modify the code below to match the columns and range in your workbook, and set the target sheet to the appropriate place.

This code autofilters your range based on user inputs and copies the results to another sheet if there is a match:

Sub Autofilter_test()
    Dim clientTable As Range
    Dim desired_emp As String
    Dim desired_fw As Integer
    Dim MatchRange As Range
    Dim tgt As Worksheet

    Set clientTable = Range("A1:B8")
    Set tgt = ThisWorkbook.Sheets("Sheet2")
    ActiveSheet.AutoFilterMode = False
    desired_emp = Application.InputBox("Select an Employee")
    desired_fw = Application.InputBox("What FW would you like to do this for?")

    With clientTable
        .AutoFilter Field:=1, Criteria1:=desired_emp
        .AutoFilter Field:=2, Criteria1:=desired_fw
    End With

    Call CopyFilteredData(tgt)

End Sub


Sub CopyFilteredData(tgt As Worksheet)
    ' by Tom Ogilvy source: http://www.contextures.com/xlautofilter03.html
    Dim rng As Range
    Dim rng2 As Range

    With ActiveSheet.AutoFilter.Range
     On Error Resume Next
       Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
           .SpecialCells(xlCellTypeVisible)
     On Error GoTo 0
    End With
    If rng2 Is Nothing Then
       MsgBox "No data to copy"
    Else
       tgt.Cells.Clear
       Set rng = ActiveSheet.AutoFilter.Range
       rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
         Destination:=tgt.Range("A1")
    End If
       ActiveSheet.ShowAllData

End Sub

Having a few issues with the CopyFilteredData but I can work that out. The AutoFilter worked perfect. Thanks! — Tyler, Jun 20 '13 at 11:55

VBA Nested If / range match

1 Answers1