NEED CORRECTION IN MACRO

[sheikh talib]

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

[mancubus]

pls dont pm for requesting help. being online doest mean im available.
members here are happy to help anyone if they can. so no pm's are required.

that said, i see you slightly modified a recorded macro.

a sample workbook might help us help you.

[mancubus]

here is a better way to insert formulas into cells.
adopt them to your requirements.

Range("E8:E" & LR).Formula = "Formula here"
Range("J8:J" & LR).Formula = "Another Formula here"
Range("K8:K" & LR).Formula = "3rd Formula here"
'...
'...
'...

Formula, FormulaArray or FormulaR1C1

[sheikh talib]

I am attaching a file, problem started for me is from module4 named "customer",& it is still not working from there it made everything worse,now my starting formula is not working too,Another thing is that can i enter a formula in a module just like i entered in macro.

[mancubus]

you are referring to another thread of yours which has been marked as solved 3 months ago.

i really dont understand why you complain about the solution i posted there as it's not working or causing problems in this thread and after 3 months.

even though i have lost the desire to provide a solution to this thread, i will post a macro which inserts formulas into cells. you should adopt it yourself to your needs.

nothing more.

[mancubus]

Sub Macro1()
Dim LR As Long
With Worksheets("INVOICE")
    LR = .Cells(.Rows.Count, 6).End(xlUp).Row
    .Range("E8:E" & LR).Formula = "=IFERROR(INDEX(Table1[ID],MATCH(G8&H8,INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
    .Range("J8:J" & LR).Formula = "=IFERROR(INDEX(Table1[s.RATE],MATCH(G8&H8,INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
    .Range("K8:K" & LR).Formula = "=IFERROR(I8*J8,"""")"
    .Range("L8:L" & LR).Formula = "=IFERROR(INDEX(Table1[s.s.disc],MATCH(G8&H8,INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
    .Range("M8:M" & LR).Formula = "=IFERROR(K8-K8*L8%,"""")"
    .Range("O8:O" & LR).Formula = "=IFERROR(INDEX(Table1[p.rate],MATCH(G8&H8,INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
    .Range("P8:P" & LR).Formula = "=IFERROR(I8*O8,"""")"
    .Range("Q8:Q" & LR).Formula = "=IFERROR(INDEX(Table1[s.p.disc],MATCH(G8&H8,INDEX(Table1[PRODUCT]&Table1[COLOR],0),0)),"""")"
    .Range("R8:R" & LR).Formula = "=IFERROR(P8-P8*Q8%,"""")"
End With
End Sub