1

I work on the code that will calculate Array Formula basing on how many records is in the column N:N that is 11 columns earlier (offset 11). I want to use the formula with array that will use the parallel row from the column N:N and copy down until the last record in column N:N exist. However, for now, formula copies down basing on the first record only instead of taking the row in parallel:

With ThisWorkbook.Sheets("Sheet1")

TargetRow = 4 

   .Range("N4", .Cells(Rows.Count, "N").End(xlUp)).Offset(0, 11).FormulaArray = "=IFERROR(Name&INDEX(Names_Area,MATCH(RC[-11],Name&Name_Origin,0),2),"""")"

    End With

I heard about fill down function or something alike but I am not sure how to insert it here. How can I fix it so when the formula copies down into rows it takes the row in parallel and not all the time N4 (that is the first row of records). I will appreciate any help.

I also want to mention that any other formula without array works and copies formula down basing on the rows in column N:N that are in paralell.

Improve this question
1
  • .AutoFill should work for the range Commented Feb 25, 2018 at 19:51

2 Answers 2

Reset to default
1

Try with .Autofill. something like:

With ThisWorkbook.Sheets("Sheet1")

    TargetRow = 4 

    .Range("N4").FormulaArray = "=IFERROR(Name&INDEX(Names_Area,MATCH(RC[-11],Name&Name_Origin,0),2),"""")"
    .Range("N4").AutoFill .Range("N4:N12")

End With

I have used an example end point of N12 for the autofill which you can adjust.

Though note you are actually going to column Y with:

.Range("N4", .Cells(Rows.Count, "N").End(xlUp)).Offset(0, 11)

So you may want to ensure you autofill and populate formula in the actual column you want to fill.

Maybe something like:

.Range("N4").Offset(0, 11).FormulaArray =

Reference:

https://www.mrexcel.com/forum/excel-questions/500971-how-copy-array-formula-down-vba-macro.html

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi QHarr, I have tried your idea using: .Range("N4", .Cells(Rows.Count, "N").End(xlUp)).Offset(0, 11).FormulaArray = "=IFERROR(RU_Project.ID&INDEX(RU_WBS_Area,MATCH(RC[-11],RU_Project.ID&RU_WBS_Origin,0),2),"""")" .Range("N4").AutoFill .Range("N4:N12" But the formula result in next rows still bases the result on the first N4, not according to row in parallel in Column N:N
you have to apply to one column I think rather than N to Y (?). Otherwise you would need the formula in the first row of all the relevant columns before doing the fill down. It fills down not across and down.
0

you could also use

With ThisWorkbook.Sheets("Sheet1")
    With .Range("N4", .Cells(Rows.Count, "N").End(xlUp)).Offset(0, 11)
        .Cells(1, 1).FormulaArray = "=IFERROR(Name&INDEX(Names_Area,MATCH(RC[-11],Name&Name_Origin,0),2),"""")"
        .Formula = .Cells(1, 1).Formula
    End With
End With
Improve this answer

4 Comments

Hi, I have tried your code but maybe I misunderstood because I didn't get the result right. By the part: '.Cells(1,1)' are you giving just an example where the result will be placed? So If my result should start in column Y, target row 4, Should the part of the code be: '.Cells(4,26)' ? or how?
Nope. .Cells(1,1) is pointing at the first cell in the _referenced_ range in the With` statement. So, in this case it's pointing to cell "N4" of "Sheet1" sheet
@Dozens, please check my code against your original question data, and if it works as per this site rules you may consider marking answer as accepted. Otherwise you can come back and detail what didn't work. While if you have to change your original question data, then you may post a new question along with your efforts
Ok, I will do that

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.