Excel vba loop to find records matching multiple search criteria and multiple sheets

[SoniaLady]

Hi @all,
I would like to implement the following function in VBA as a procedure.

=INDEX(Zones!$G$3:$S$273;MATCH($A3;Zones!$C$3:$C$272);MATCH($B3;Zones!$G$2:$S$2))

I have two sheets ("Data" and Zones"). I search in the sheet Zones for zone numbers, to be able to determine later also the prices. The search criteria are country and delivery service is in the sheet "Data A2:A" and delivery service in "Data B2:B"). The country in the data sheet "Zones" is in column A, the delivery service in rows G2:S2. The searched zones numbers are in G3:S272). I would appreciate it very much if someone could help here.

Many thanks in advance.
Regards
Sonia

 

[SoniaLady]

Sub test()

    With rngTarget
        .Value = "=WorksheetFunction.Index(rngMatrix, WorksheetFunction.Match(strName, rngColumn, 0), WorksheetFunction.Match(Zones, rngRow, 0))"
       ' .Value = .Value
    End With
End Sub

You should have a formula in C2

mohadin. The I get the following result:

 

[SoniaLady]

The following code works well:

Sub GetZonesSec()
    Dim wSSource As Worksheet: Set wSSource = Worksheets("Zones")
    Dim wSTarget As Worksheet: Set wSTarget = Worksheets("Data")
    Dim rngMatrix As Range: Set rngMatrix = wSSource.Range("C3:S273")
    Dim rngRow As Range: Set rngRow = wSSource.Range("C2:S2")
    Dim rngColumn As Range: Set rngColumn = wSSource.Range("C3:C272")
    Dim rngTarget As Range: Set rngTarget = wSTarget.Range("C2")
    
    
    Dim strName As String: strName = Worksheets("Data").Range("A2")
    Dim Zones As String: Zones = Worksheets("Data").Range("B2")
    If Not IsError(Application.Match(strName, rngColumn, 0)) And Not IsError(Application.Match(Zones, rngRow, 0)) Then
  
        rngTarget.Value = Application.Index(rngMatrix, _
                            Application.Match(strName, rngColumn, 0), Application.Match(Zones, rngRow, 0))
   End If
End Sub

Maybe it can still be useful to someone.

 

[mohadin]

With rngTarget
.Formula = "=Index(rngMatrix, .Match(strName, rngColumn, 0), Match(Zones, rngRow, 0))"
.Value = .Value
End With