Forecast Match and Index in VBA

[chenyu768]

I'm trying to simplify some really long formulas in my model by creating some custom functions. I've cleaned up a lot but there's this forecast with offset and match that's throwing me off.

here's the formula

=FORECAST(AE1264,OFFSET('Data Table Dynamic'!$AC$4,MATCH(AE1264,'Data Table Dynamic'!$AB$4:$AB$23,-1)-1,0,3),OFFSET('Data Table Dynamic'!$AB$4,MATCH(AE1264,'Data Table Dynamic'!$AB$4:$AB$23,-1)-1,0,3)))

There's actually 4 of those per cell that's looking at differnt tables so as you can see it gets really long and confusing.

i tried simplfy it by using this

Function LT_Inj(inv, DT_Inv, DT_Inj_Cap, DT_rng)




LT_Inj = WorksheetFunction.Forecast(inv, WorksheetFunction.Offset(DT_Inj_Cap, WorksheetFunction.Match(inv, DT_rng, -1) - 1, 0, 3), WorksheetFunction.Offset(DT_Inv, WorksheetFunction.Match(inv, DT_rng, -1) - 1, 0, 3))


but it's giving me an error.

what am i doing wrong here and also is there a better way than using worksheetFunction?