Formula Array Run-Time Error 1004

[ldmcd]

Hi,

I'm trying to insert this formula

=IF(ISNA(INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3,Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D,0))),""No Commit Data"",INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3,Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D,0)))"

As an array in VBA. I have tried breaking it down as I've seen in other threads, like below:

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String

theFormulaPart1 = "=IF(ISNA(INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3,Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D,0))),""No Commit Data"",""X_X_X"""
theFormulaPart2 = ",INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3,Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D,0)))"

With Range("F4")
.FormulaArray = theFormulaPart1
.Replace """X_X_X""", theFormulaPart2
End With


No matter what I do, I can't get the error to go away. I've tried multiple things and now I'm at my wit's end. This is literally the last thing I need to finish this project and it's driving me mad. If anyone can please tell me what I'm missing, that would be awesome!

 

[DanteAmor]

Hi and welcome to MrExcel.

How about:

Sub test()
  Dim f1 As String, f2 As String, f3 As String
  f1 = "=IF(ISNA(INDEX(Pivot!$D:$D,MATCH(x_x_x,y_y_y,0))),""No Commit Data"",INDEX(Pivot!$D:$D,MATCH(x_x_x,y_y_y,0)))"
  f2 = "'Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3"
  f3 = "Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D"
  With Range("F4")
    .FormulaArray = f1
    .Replace "x_x_x", f2
    .Replace "y_y_y", f3
  End With
End Sub

But it may be necessary to review your formula.
According to my test the value you have in GT3 is the result.
But maybe I'm wrong.

You are also putting in your array formula the selection of the whole columns in A, E and D, that makes your sheet slower. You should limit.

Lastly, if you're already working on a macro, maybe you could do a macro to do the search and put the result instead of having formulas.

 

[ldmcd]

Thank you so much! On first test this works!

I had originally tried to use loops and for each statements to fill out this sheet, but it all got too confused and wasn't populating the data in the right places. The key is I'm going by UID and putting data into the sheet based on fiscal week and what type of data it is - date, qty, etc. For the life of me, I couldn't get my loops to work quite right, so I had to find another way. Index/Match, while slow, at least gets the job done properly. I'm hoping I can eventually retool to something that's more usable, but I've been working on this deliverable for a week or so now and I just need to get it in the hands of the people that needed it, no matter how slow - it's still better than anything they have right now (not tooting my own horn, what they have now lumps everything up into quarters only and that makes it hard when trying to talk individual fiscal weeks with suppliers).

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.