# Application.WorksheetFunction INDEX/MATCH VBA formula with Offset?

#### shella

##### New Member
Hey all! Is it possible to use ACTIVECELL.OFFSET combined with the Application.WorksheetFunction INDEX/MATCH formula? I tried R1C1 without success and I can't refer to the cell explicitly, as I'd like to loop through a range and apply the formula if the conditions are met. So far, here's what I've got...

Code:
``````Sub InchstoneFormula()
Dim i As Range

For Each i In Sheet4.Range("E4:E5")
If i.Offset(0, -2) <> "" Or i.Offset(0, 3) <> "" Then
i.Formula = Application.WorksheetFunction.Index(Sheets("Inchstone").Range("E4:E504"), Application.WorksheetFunction.Match((i.Offset(0, -2)), Sheets("Inchstone").Range("G4:G504"), 0) + (i.Offset(0, -1)))
End If
Next i
End Sub``````

I'm getting the subscript out of range error right now, but was also getting an object error earlier that I'm confident still needs addressed as well. Thoughts?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### baitmaster

##### Well-known Member
I think you're getting confused between the different types of formulas available. You can either calculate something in VBA and pass the result across, or you can write an Excel formula into a cell and have Excel calculate it. The VBA approach can be quicker but is less easy to audit and check, so is not recommended

To write a formula (range.formula or range.formulaR1C1) you want to pass a text string into the cell

To calculate in VBA you can calculate from first principles (+/- etc), use basic VBA formulas (e.g. MIN, LEN) or take shortcuts by referring to the worksheetfunction object, which gives VBA acess to some of Excel's main formulas

In your case I think you're trying to pass VBA objects into an Excel formula. What will actually happen is that VBA will try to evaluate the calculation and pass only the value over. So even if your current approach works, you're only going to pass a number over - but you've said you want to write a formula

So what do you want to do?
- write an Excel formula? - in which case, record a macro containing the different possible formulas, and see what the text string looks like that you're aiming to recreate. Paste it here so I can see it
- calculate in VBA? I'd recommend breaking down into several parts so we can test each bit and decide whether to proceed - e.g. do the MATCH to make sure the item is found before trying to do the INDEX

#### shella

##### New Member
Baitmaster, Apologies for the delay in responding to you. Your clarification above was PERFECT and I was able to get it worked out based on your input. Thanks so much!

Replies
2
Views
142
Replies
3
Views
279
Replies
1
Views
296
Replies
8
Views
115
Replies
15
Views
418

1,191,687
Messages
5,988,019
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back