CODEWITHSUNDEEP
vbaexcelexcel-formula
Adam
Adam

Reputation: 1

How do I fix the following to make it sequential?

I was wondering if you could help with the following. I have just started using VBA but I am having an issue with applying the following formula to the dataset. 

Selection.Formula = "=IFNA(INDEX('beach'!B:B,MATCH(F19,'beach'!$G:$G,0)),0)"

F19 is a set cell, however, I need F19 to be sequential, as in that when the calculation is completed in cell M20 rather than M19, the formula is updated to calculate using F20.

Also, following on from that how do I edit the above formula to allow the macros to be applied to all cells in a Column?

Upvotes: 0

Views: 48

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

You can also use R1C1 style.

Say you want that formula to apply in range M19:M20 with your F19 reference updating to each row:

Range("M19:M20").FormulaR1C1 = "=IFNA(INDEX(beach!C[-11],MATCH(RC[-7],beach!C7,0)),0)"

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96763

Something like:

Sub poiuyt()
    Range("M19:M20").Formula = "=IFNA(INDEX('beach'!B:B,MATCH(F19,'beach'!$G:$G,0)),0)"
End Sub

will auto-adjust the F index:

enter image description here

Upvotes: 2

Related Questions