Help Amending VBA code

[purceld2]

Hi Guy's

I am quick new to VBA code and bit rusty.

I am trying to amend the VBA Code my being a fixed last row to a dynamic i.e using the lastrow variable.

I just cannot seem to get the syntax right.

Your help would be greatly appreciated.

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
     
    Range("D2").FormulaArray = "=IFERROR(INDEX(Assignment!$H$3:$H$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
    Range("E2").FormulaArray = "=IFERROR(INDEX(Assignment!$D$3:$D$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
    Range("D2:E" & lastrow).FillDown

 

[BigBeachBananas]

Try:

 Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(INDEX(Assignment!$H$3:$H$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
Range("E2").Select
   ActiveCell.FormulaR1C1  = "=IFERROR(INDEX(Assignment!$D$3:$D$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
    Selection.AutoFill Destination:=Range("M2:E" & Range("A" & Rows.count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

 

[purceld2]

Try:

 Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(INDEX(Assignment!$D$3:$D$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
Range("E2").Select
   ActiveCell.FormulaR1C1  = "=IFERROR(INDEX(Assignment!$D$3:$D$429,MATCH(1,(A2=Assignment!$A$3:$A$429)*($D$1=Assignment!$H$3:$H$429),0)),"""")"
    Selection.AutoFill Destination:=Range("M2:E" & Range("A" & Rows.count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

Hi BigBeachBananas

I am sorry if i did not explain my question correctly

I was referring to the !$D$3:$D$429, $A$3:$A$429, $H$3:$H$429 parts of the code.

Regards

 

[BigBeachBananas]

Hi BigBeachBananas

I am sorry if i did not explain my question correctly

I was referring to the !$D$3:$D$429, $A$3:$A$429, $H$3:$H$429 parts of the code.

Regards

No problem, I think you need to use & and "" to concatenate. I don't have excel to test atm but: something like this:

 Range("D2").FormulaArray = "=IFERROR(INDEX(Assignment!$H$3:$H$" & lastrow & ",MATCH(1,(A2=Assignment!$A$3:$A$"& last row & ")*($D$1=Assignment!$H$3:$H$" & lastrow & "),0)),"""")"

 

[Alex Blakenburg]

Using a place holder and then the replace command seems quite a popular way of achieving this.
In the below "#" is the place holder for the last row number.

    Range("D2").FormulaArray = Replace( _
                                    "=IFERROR(INDEX(Assignment!$H$3:$H$#,MATCH(1,(A2=Assignment!$A$3:$A$#)*($D$1=Assignment!$H$3:$H$#),0)),"""")", _
                                    "#", lastrow)
    Range("E2").FormulaArray = Replace( _
                                    "=IFERROR(INDEX(Assignment!$D$3:$D$#,MATCH(1,(A2=Assignment!$A$3:$A$#)*($D$1=Assignment!$H$3:$H$#),0)),"""")", _
                                    "#", lastrow)

 

[purceld2]

Using a place holder and then the replace command seems quite a popular way of achieving this.
In the below "#" is the place holder for the last row number.

    Range("D2").FormulaArray = Replace( _
                                    "=IFERROR(INDEX(Assignment!$H$3:$H$#,MATCH(1,(A2=Assignment!$A$3:$A$#)*($D$1=Assignment!$H$3:$H$#),0)),"""")", _
                                    "#", lastrow)
    Range("E2").FormulaArray = Replace( _
                                    "=IFERROR(INDEX(Assignment!$D$3:$D$#,MATCH(1,(A2=Assignment!$A$3:$A$#)*($D$1=Assignment!$H$3:$H$#),0)),"""")", _
                                    "#", lastrow)

Hi Alex
I hope you don't mind me reaching out to you.
You so kindly helped me a while back to insert the above formula into a range, and I have been trying to do the same with the formula below and just cannot get it to work.
Would mind helping me please?
This the formula in C2 =MAX(INDEX((A2=Assignment!$A$3:$A$431)*Assignment!$F$3:$F$431,0))

Thank again

 

[Alex Blakenburg]

This probably should be a new thread but see if this is what you are after:-

    Range("C2").FormulaArray = Replace( _
                                    "=IFERROR(MAX(INDEX((A2=Assignment!$A$3:$A$#)*Assignment!$F$3:$F$#,0)),"""")", _
                                    "#", lastrow)

 

[purceld2]

This probably should be a new thread but see if this is what you are after:-

    Range("C2").FormulaArray = Replace( _
                                    "=IFERROR(MAX(INDEX((A2=Assignment!$A$3:$A$#)*Assignment!$F$3:$F$#,0)),"""")", _
                                    "#", lastrow)

Thank you much appreciated.

Will start a new post next time.

Regards
Desmond

 

[Alex Blakenburg]

No problem. Thanks for the feedback, glad I could help.