#### cheesy_goodness

##### New Member

- Joined
- May 19, 2014

- Messages
- 24

The workbook I'm working on is a model that, in part, imports data from another workbook. The formula array I'm having trouble with is a simple Index/Match formula, but it is rather lengthy because 1) the path to the other workbook is long and 2) I'm matching three values, so the path has to be in the formula a total of 4 times (once for the Index function and 3 times for the Match function).

The formula is meant to be put in each cell within a certain range. The formula for one cell is below (note the paths and range names in the other workbook have been altered for this example)

Code:

`=INDEX('H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Survey_Results_Totals,MATCH(N3&O3&P3,'H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Branch_Name&H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Company_Division&H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Company_Category,0))`

The line of the macro that's supposed to put the formula array in the workbook is below, where SurveyFormulaX is some part of the string above.

Code:

```
For Each cell In ActiveSheet.Range(ForMac.Range("Range_For_1st_Number_Of_Hours_Spent_Originating_Survey_Responses").Value)
With cell
.FormulaArray = "=" & SurveyFormula1 & SurveyFormula2 & cell.Row & "&" & SurveyFormula3 & cell.Row & "&" & SurveyFormula4 & cell.Row & SurveyFormula5
End With
```

Thank you!