Application.WorksheetFunction INDEX/MATCH VBA formula with Offset?

[shella]

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...

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?

Thanks in advance!!

 

[baitmaster]

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]

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!