sheikh talib
07-18-2014, 01:21 AM
Hi everyone,I was using a macro in a workbook,it was working fine but now it works for the 1st row & copies it down to last row when i enter save option it works.Can someone please correct it & make it in a good shape so that i can use it at any other place.
Sub sinoformula()
'
' sinoformula Macro
Dim Lr As Long
Lr = ThisWorkbook.Sheets("INVOICE").Cells(Rows.Count, 6).End(xlUp).Row
'
Range("E8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[ID],MATCH(RC[2]&RC[3],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("E8:E" & Lr), Type:=xlFillDefault
Range("E8:E" & Lr).Select
Range("J8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.RATE],MATCH(RC[-3]&RC[-2],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("J8:J" & Lr), Type:=xlFillDefault
Range("J8:J" & Lr).Select
Range("K8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Range("K8").Select
Selection.AutoFill Destination:=Range("K8:K" & Lr), Type:=xlFillDefault
Range("K8:K" & Lr).Select
Range("L8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.s.disc],MATCH(RC[-5]&RC[-4],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("L8:L" & Lr), Type:=xlFillDefault
Range("L8:L" & Lr).Select
Range("M8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]-RC[-2]*RC[-1]%,"""")"
Range("M8").Select
Selection.AutoFill Destination:=Range("M8:M" & Lr), Type:=xlFillDefault
Range("M8:M" & Lr).Select
Range("O8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[p.rate],MATCH(RC[-8]&RC[-7],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("O8:O" & Lr), Type:=xlFillDefault
Range("O8:O" & Lr).Select
Range("P8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-7]*RC[-1],"""")"
Range("P8").Select
Selection.AutoFill Destination:=Range("P8:P" & Lr), Type:=xlFillDefault
Range("P8:P" & Lr).Select
Range("Q8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.p.disc],MATCH(RC[-10]&RC[-9],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("Q8:Q" & Lr), Type:=xlFillDefault
Range("Q8:Q" & Lr).Select
Range("R8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]-RC[-2]*RC[-1]%,"""")"
Range("R8").Select
Selection.AutoFill Destination:=Range("R8:R" & Lr), Type:=xlFillDefault
Range("R8:R" & Lr).Select
End Sub
Sub sinoformula()
'
' sinoformula Macro
Dim Lr As Long
Lr = ThisWorkbook.Sheets("INVOICE").Cells(Rows.Count, 6).End(xlUp).Row
'
Range("E8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[ID],MATCH(RC[2]&RC[3],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("E8:E" & Lr), Type:=xlFillDefault
Range("E8:E" & Lr).Select
Range("J8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.RATE],MATCH(RC[-3]&RC[-2],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("J8:J" & Lr), Type:=xlFillDefault
Range("J8:J" & Lr).Select
Range("K8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Range("K8").Select
Selection.AutoFill Destination:=Range("K8:K" & Lr), Type:=xlFillDefault
Range("K8:K" & Lr).Select
Range("L8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.s.disc],MATCH(RC[-5]&RC[-4],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("L8:L" & Lr), Type:=xlFillDefault
Range("L8:L" & Lr).Select
Range("M8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]-RC[-2]*RC[-1]%,"""")"
Range("M8").Select
Selection.AutoFill Destination:=Range("M8:M" & Lr), Type:=xlFillDefault
Range("M8:M" & Lr).Select
Range("O8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[p.rate],MATCH(RC[-8]&RC[-7],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("O8:O" & Lr), Type:=xlFillDefault
Range("O8:O" & Lr).Select
Range("P8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-7]*RC[-1],"""")"
Range("P8").Select
Selection.AutoFill Destination:=Range("P8:P" & Lr), Type:=xlFillDefault
Range("P8:P" & Lr).Select
Range("Q8").Select
Selection.FormulaArray = _
"=IFERROR(INDEX(Table1[s.p.disc],MATCH(RC[-10]&RC[-9],INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
Selection.AutoFill Destination:=Range("Q8:Q" & Lr), Type:=xlFillDefault
Range("Q8:Q" & Lr).Select
Range("R8").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]-RC[-2]*RC[-1]%,"""")"
Range("R8").Select
Selection.AutoFill Destination:=Range("R8:R" & Lr), Type:=xlFillDefault
Range("R8:R" & Lr).Select
End Sub