Convert Index/match to vba

[JannetteChristie]

Hello,

I am having trouble converting the following excel function to vba:
=INDEX('Product Selector'!F13:F18,MATCH(Specify!B10,'Product Selector'!A13:A18,0),1)

The vba code I have is as follows:
iRowNo = 10
For i = 1 To lr
x = Application.WorksheetFunction.Index(Sheets("Product Selector").Range("A13:A100") _
, Application.WorksheetFunction.Match(Sheets("Specify").Cells(iRowNo, 2), Sheets("Product Selector").Range("F3:F100"), 0), 1)

If Not (IsError(x)) Then
Worksheets("Specify").Cells(iRowNo, 4).Value = x
End If
iRowNo = iRowNo + 1
Next i

I keep getting a run-time error '1004'

 

[Fluff]

Try removing the two WorksheetFunction in the formula

 

[JannetteChristie]

When I remove them I get a Run-time error 13, type mismatch

 

[Fluff]

What have you declared x as ?

 

[JannetteChristie]

Declared as a string

 

[Fluff]

It needs to be Variant, also the ranges in the formula don't match up, you have A13:A100 & F3:F100

 

[JannetteChristie]

The range A13:A100 is the range that has the match lookup, the range F13:F100 is the data I want to return from the index as per the excel formula =INDEX('Product Selector'!F13:F18,MATCH(Specify!B10,'Product Selector'!A13:A18,0),1)

 

[Fluff]

Compare that formula to the one in the code.
They are very different!

 

[Fluff]

Give this a go

Sub jannetteChristie()
   Dim x As Variant
   Dim Pws As Worksheet, Sws As Worksheet
   
   Set Pws = Sheets("Product Selector")
   Set Sws = Sheets("Specify")
   iRowNo = 10
   For i = 1 To 10
      With Application
         x = .Index(Pws.Range("F13:A100"), .Match(Sws.Cells(iRowNo, 2), Pws.Range("A13:A100"), 0))
      End With
      If Not IsError(x) Then
         Sws.Cells(iRowNo, 4).Value = x
      End If
      iRowNo = iRowNo + 1
   Next i
End Sub

 

[JannetteChristie]

Morning,
Thanks for your reply - when I use the code the "Specify" sheet is not being updated, it is returning an error every time