PDA

View Full Version : NEED CORRECTION IN MACRO



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

mancubus
07-18-2014, 01:57 AM
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
07-18-2014, 02:00 AM
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
07-18-2014, 02:58 AM
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
07-18-2014, 04:40 AM
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
07-18-2014, 04:43 AM
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