Array/Index Formula result always 0

[iainmartin100]

Hi,
I have a 25mb spreadsheet which runs quickly and without fault (the bulk of the spreadsheet in Macro text).

I'm running a Macro which inserts an array/index formula into the spreadsheet (eg formula below), this is runs perfectly and gives the desired result when running all months of the year until I hit December when the result always comes back as "0", I have checked the source document and there is data with a value.

My first assumption is that either Array or Index has hit an excel limit, although I would expect to get a #Val or simular but because I get a 0 value it would suggest that may not be the case?

Can anyone see any errors or limits I have hit, plus any suggestions of how to over come this?

Thanks for looking

Sub Formula_FOC_12()
Dim wb As Variant
Dim wb2 As Variant
Dim WW As Variant
Dim wbN As String

wb = ActiveWorkbook.Name
WW = ThisWorkbook.LinkSources(xlExcelLinks)
wbN = Right(WW(1), Len(WW(1)) - InStrRev(WW(1), "\"))

Range("h400").FormulaArray = "=INDEX('[" & wbN & "]MS Groups'!r4678c6:r5857c6,MATCH(r7c8&r400c3,'[" & wbN & "]MS Groups'!r4678c3:r5857c3&'[" & wbN & "]MS Groups'!r4678c4:r5857c4))"

 

[Andrew Poulsom]

Maybe you need to add a FALSE argument to MATCH. If that still doesn't work please post some sample data with the expected result.

 

[iainmartin100]

Hi, thanks for the reply.

I use identical formula's which work 100%, the only variable is the range im using. The range is looking at an external document r4678c6:r5857c6, would this cause any issues because of the row number?

Many thanks

 

[Andrew Poulsom]

You problem isn't caused by the row numbers. Did you try adding a FALSE argument? Your formula currently assumes that your data is sorted ascending. As I said before post some sample data if it still doesn't work.

 

[iainmartin100]

Sorry im a bit of a beginner using match & Array, is there any chance you can point me where I should try and add the FALSE into this as I keep making errors?

Range("h400").FormulaArray = "=INDEX('[" & wbN & "]MS Groups'!r4678c6:r5857c6,MATCH(r7c8&r400c3,'[" & wbN & "]MS Groups'!r4678c3:r5857c3&'[" & wbN & "]MS Groups'!r4678c4:r5857c4))"

Many thanks for your help

 

[Andrew Poulsom]

Like this:

Range("h400").FormulaArray = "=INDEX('[" & wbN & "]MS Groups'!r4678c6:r5857c6,MATCH(r7c8&r400c3,'[" & wbN & "]MS Groups'!r4678c3:r5857c3&'[" & wbN & "]MS Groups'!r4678c4:r5857c4,FALSE))"

 

[iainmartin100]

Thanks so much for all the help, it now work perfectly for all occations.
Iain