Hi everyone
I have a problem to copy and paste in the index match formula to the last row. I suppose I2 and J2 formulas should be pasted in until the last row. however I noticed the macro below doesn't refer to the right cell after paste:
I expect the formula '=IFERROR(INDEX(Cost!$A:$C,MATCH('V&A'!$E2,Cost!$A:$A,FALSE),3),"")' is in cell I2 and J2 then paste down to the last row of columns I and J respectively, so in I3 and J3 the formula will be update to E3 automatically. However, after I have run the macro, some formulas are not updated correctly based on the row number, e.g. in row 3 it refers to V&A E22 rather than V&A E3. If I pasted down manually then they are okay. So I wonder is there something wrong with my macro? Many thanks.
I have a problem to copy and paste in the index match formula to the last row. I suppose I2 and J2 formulas should be pasted in until the last row. however I noticed the macro below doesn't refer to the right cell after paste:
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastRow As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]LastRow = Range("A" &Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("I2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveCell.FormulaR1C1 = _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] "=IFERROR(INDEX(Cost!C1:C3,MATCH('V&A'!RC5,Cost!C1,FALSE),3),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("J2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] ActiveCell.FormulaR1C1= _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] "=IFERROR(INDEX(Cost!C1:C3,MATCH('V&A'!RC5,Cost!C1,FALSE),3),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("M2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] ActiveCell.FormulaR1C1 = "=IFERROR(RC8*RC9,0)"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("I2:I" & LastRow).FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("J2:J" & LastRow).FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("M2:M" & LastRow).FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("M1").Value = "Total Cost"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("I:I,J:J,M:M").NumberFormat = "0.00"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] 'Format Total[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("L1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Selection.Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Range("M1").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] SkipBlanks:=False, Transpose:=False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Application.CutCopyMode = False[/COLOR][/SIZE][/FONT]
I expect the formula '=IFERROR(INDEX(Cost!$A:$C,MATCH('V&A'!$E2,Cost!$A:$A,FALSE),3),"")' is in cell I2 and J2 then paste down to the last row of columns I and J respectively, so in I3 and J3 the formula will be update to E3 automatically. However, after I have run the macro, some formulas are not updated correctly based on the row number, e.g. in row 3 it refers to V&A E22 rather than V&A E3. If I pasted down manually then they are okay. So I wonder is there something wrong with my macro? Many thanks.