How to use Index function in coding macro?

[Eric]

Does anyone have any suggestions on how to use index function in coding macro?

myCell.Offset(0, 2).Value =
Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp
worksheet],0))

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Mike H]

Eric,

The workaheet formula
=INDEX(B1:B9,MATCH(A1,Sheet2!D19,FALSE),1)
can be used in macro like this

Lvalue = Sheets("Sheet1").Range("A1").Value
Set IRange = Sheets("Sheet1").Range("B1:B9")
Set Vrange = Sheets("Sheet2").Range("D19")
Set Mycell = Sheets("Sheet2").Range("D1")

Mycell.Offset(0, 2).Value = Application.WorksheetFunction.Index _
(IRange, Application.WorksheetFunction.Match(Lvalue, Vrange, False), 1)

Mike

 

[Peter T]

There are just a handful of worksheet functions that can be called directly
from 'Application' more efficiently than from 'WorksheetFunction', eg Index
and Match in the following context -

Sub Test()
Dim r1 As Range, r2 As Range, r3 As Range
Dim LookUpVal As Variant

SampleData
Set r1 = Range("A1:A10")
Set r2 = Range("B1:B10")
Set r3 = Range("C1")

For i = 1 To 10
LookUpVal = i
Cells(i, 3) = i
Set r3 = Cells(i, 4)

With Application
r3.Value = .Index(r2, .Match(LookUpVal, r1, 0))
End With
Next

End Sub

Sub SampleData()
Dim i As Long
For i = 1 To 10
Cells(1, 1) = Int(Rnd() * 11)
Cells(i, 2) = Chr(64 + i)
Next
End Sub

There's a second advantage in bypassing 'WorkSheetFunction' in the above -
if there is no match an error value is returned rather than the code
breaking due to error.

Regards,
Peter T