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]

Nobody here who can help. Apologize my impatience

 

[SoniaLady]

I have now created the following code myself, but I get the error message: "Unable to get tech Match property of the WorksheetFunction class". Can anyone help to fix that?

Public Sub GetZones()
    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:S272")
    Dim rngRow As Range: Set rngRow = wSSource.Range("G2:S2")
    Dim rngColumn As Range: Set rngColumn = wSSource.Range("A3:A272")
    Dim rngTarget As Range: Set rngTarget = wSTarget.Range("C2")
   
    Dim strName As String: strName = Worksheets("Data").Cells(1, 1)
    Dim Zones As String: Zones = Worksheets("Data").Cells(2, 1)
   
        rngTarget.Value = WorksheetFunction.Index(rngMatrix, _
                            WorksheetFunction.Match(strName, rngColumn, 0), WorksheetFunction.Match(Zones, rngRow, 0))
                           
End Sub

 

[mohadin]

Hi
Replace

 rngTarget.Value = WorksheetFunction.Index(rngMatrix, _
                            WorksheetFunction.Match(strName, rngColumn, 0), WorksheetFunction.Match(Zones, rngRow, 0))

With

With  rngTarget
.Formula ="= WorksheetFunction.Index(rngMatrix, _
                            WorksheetFunction.Match(strName, rngColumn, 0), WorksheetFunction.Match(Zones, rngRow, 0))"
.value=.value
End with

Not tested

 

[SoniaLady]

Thanks mohadin. Now the code goes through, but as result I get #NAME?. What is wrong now?

 

[mohadin]

Try this way
Updated

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

 

[SoniaLady]

Done. Unfortunately the same result #NAME?

 

[mohadin]

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

 

[bebo021999]

Use Application instead of WorksheetFunction

Public Sub GetZones()
    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:S272")
    Dim rngRow As Range: Set rngRow = wSSource.Range("C2:S2")
    Dim rngColumn As Range: Set rngColumn = wSSource.Range("A3:A272")
    Dim rngTarget As Range: Set rngTarget = wSTarget.Range("C2")
  
    Dim strName As String: strName = Worksheets("Data").Cells(1, 1)
    Dim Zones As String: Zones = Worksheets("Data").Cells(2, 1)
    '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

You may need to add "If Not IsError...", to avoid error.

 

[SoniaLady]

Use Application instead of WorksheetFunction

Public Sub GetZones()
    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:S272")
    Dim rngRow As Range: Set rngRow = wSSource.Range("C2:S2")
    Dim rngColumn As Range: Set rngColumn = wSSource.Range("A3:A272")
    Dim rngTarget As Range: Set rngTarget = wSTarget.Range("C2")
 
    Dim strName As String: strName = Worksheets("Data").Cells(1, 1)
    Dim Zones As String: Zones = Worksheets("Data").Cells(2, 1)
    '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

You may need to add "If Not IsError...", to avoid error.

bebo02199 thank you very much for your hint and the code. I tried your code but unfortunately no value is returned. Here how my data sheet looks like.The zone number/value should appear in C2. The zone number for Brasil should be 10.