Apply Index function in VBA Excel

index function vba excel

Objective

To apply Index function in VBA Excel.

Approach

Index function basically return the value at a particular position in a range. Suppose we have a range A2:B6, and we want to find which value is present at 2nd row and 2nd column then we shall apply the formula as index (A2:B6,2,2).

In the image below we can see that we had data in column A and B. We have added some dummy data in both the columns for demonstration purpose. We have selected the array for values as A2:B6, here we wanted to find the value which is stored in a cell which intersects at 2nd row and 2nd column. We can see the result in cell D2, so the result is cell “A2”, which can be seen in the image below.

We are applying the formula in cell D2 here.

 

index function vba excel

 

Code

Sub ApplyIndexFunction()

ArrayRange = "A2:B6"
Row_Num = 2
Col_Num = 2

Range("D2").Formula = "=index(" & ArrayRange & "," & Row_Num & "," & Col_Num & ")"

End Sub