Multiple criteria search in a range of amounts using VBA in

[raindrop]

Hi !

I want to search multiple criteria from sheet2 and sheet3 in a range between two amounts than copy-pasting on active sheet1.
Herewith I am attaching sample. Can anybody help me?

Thank You.

Raindrop

[HansV]

Welcome to Eileen's Lounge!

You can assign the following macros to the command buttons. Don't forget to save the workbook as a macro-enabled workbook (.xlsm)!

Code: Select all

Sub Clear()
    Range("C4:H4,C8:G21").ClearContents
End Sub

Sub Search()
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim f As Boolean
    Dim t As Long
    ' Clear result
    Range("C8:G21").ClearContents
    ' Initialize target row
    t = 7
    For Each wsh In Worksheets(Array("Sheet2", "Sheet3"))
        ' Last used row
        m = wsh.Cells(wsh.Rows.Count, 2).End(xlUp).Row
        ' Loop through rows
        For r = 3 To m
            f = True
            ' Test all conditions
            For c = 3 To 6
                If Cells(4, c) <> "" Then
                    If wsh.Cells(r, c - 1) <> Cells(4, c) Then
                        f = False
                        GoTo Check
                    End If
                End If
            Next c
            If Cells(4, 7) <> "" Then
                If wsh.Cells(r, 6) < Cells(4, 7) Then
                    f = False
                    GoTo Check
                End If
            End If
            If Cells(4, 8) <> "" Then
                If wsh.Cells(r, 6) > Cells(4, 8) Then
                    f = False
                    GoTo Check
                End If
            End If
Check:
            If f Then
                ' Match!
                t = t + 1
                Cells(t, 3).Resize(1, 5).Value = wsh.Cells(r, 2).Resize(1, 5).Value
            End If
        Next r
    Next wsh
End Sub

[raindrop]

Thank You Very Much Sir for your quick reply. It works ! But I am sorry, I had a mistake to explain about a range of two amounts as stated in table 35 and 70.. Actually it could any two numbers for a range to find other criteria instead of between 35 and 70. Once Again Sorry!

[raindrop]

Solved ! Thank you Soooo Much HansV. Really Appreciated.