Sumifs花了很长时间 [英] Sumifs taking very long time
问题描述
HI，
我尝试为SUMIFS录制宏，但是，运行报告需要很长时间，总结一下，总共有三个宏两个宏根据表中的特定条件（来自Magento和OMS工作簿）
和第三个Marco运行对帐来查找值。
运行第三个宏时excel几乎陷入困境。我的数据超过200,000行或更多
是否有任何改进代码的建议。
请提示。
问候
< g class =" gr_ gr_43 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace"数据-GR-ID =" 43" id =" 43"> aleem< / g>
选项明确
Sub Reco（）
'
'Reco Macro
'
'
Application.ScreenUpdating = False
Windows（" Magento.xlsx"）。激活
范围（"C：C，J：J，N：N"）。选择
范围（"N1"）。激活
范围（"C：C，J： J，N：N，AI：AI"）。选择
范围（"AI1"）。激活
Selection.Copy
Windows（"Reconciliation File.xlsm"）。激活
Sheets（"Magento"）。选择
列（"A：A"）。选择
Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _
：= False，Transpose：= False
Range（"A5"）。选择
Application.CutCopyMode = False
Selection.Copy
列（"A：A"） .Select
Selection.PasteSpecial Paste：= xlPasteFormats，Operation：= xlNone，_
SkipBlank s：= False，Transpose：= False
Application.CutCopyMode = False
Range（" A6"）。选择
Windows（" OMS.xlsx"）。激活
ActiveWindow.ScrollColumn = 19
范围（"D：D，E：E，U：U，X：X"）。选择
范围（" X1"）。激活
选择。复制
Windows（"Reconciliation File.xlsm"）。激活
表格（"OMS"）。选择
列（"A：A"）。选择
Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
：= False，Transpose：= False
Range（" A2"）。Select
Application.CutCopyMode = False
Selection.Copy
列（"A：A"）。选择
Selection.PasteSpecial粘贴：= xlPasteFormats，操作：= xlNone，_
SkipBlanks：= False，转置： = False
Application.CutCopyMode = False
范围（"A5"）。选择
表格（"Magento"）。选择
列（"A：B&qu" ot;）。选择
Selection.Copy
表格（"对帐"）。选择
列（"A：A"）。选择
ActiveSheet.Paste
Sheets（"OMS"）。选择
Range（"A2：B2"）。选择
Range（Selection，Selection.End（xlDown））。选择
Range（选择， Selection.End（xlDown））。选择
Range（Selection，Selection.End（xlDown））。选择
Range（Selection，Selection.End（xlDown））。选择
Range（选择） ，Selection.End（xlUp））。选择
Application.CutCopyMode = False
Selection.Copy
Sheets（"Reconciliation"）。选择
Range（" A2"） .Select
Selection.End（xlDown）.Select
Selection.End（xlDown）.Select
Selection.End（xlDown）.Select
Selection.End（xlUp）.Select
Cells（Rows.Count，1）.End（xlUp）.Offset（1,0）.Select
Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
： = False，Transpose：= Fal se
列（"A：B"）。选择
Application.CutCopyMode = False
ActiveSheet.Range（" $ A $ 1：$ B $ 100000"）。RemoveDuplicates Columns：=数组（1,2），_
标题：= xlNo
范围（" C3"）。选择
ActiveCell.FormulaR1C1 = _
" = SUMIFS（Magento！C4 ，Magento的C2，和解RC2，Magento的C3，和解R2C）QUOT！;！
范围（"C3"）。选择
Selection.Copy
范围（"C3：G3"）。选择
ActiveSheet.Paste
范围（" H3"）。选择
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =" = SUMIFS（OMS！C4，OMS！C2，RC2，OMS！C3，R2C）"
范围（"H3"）。选择
Selection.Copy
范围（" H3：L3"）。选择
ActiveSheet.Paste
范围（" C3：L3"）。选择
Application.CutCopyMode = False
Selection.Copy
范围（" B3"）。选择
Selection.End（xlDown）。选择
Cells（Rows.Count，1）.End（xlUp）.Offset（0,2）.Select
Range（Selection，Selection.End（xlUp））。选择
ActiveSheet.Paste
ActiveWindow.SmallScroll down：= - 140
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Columns（" A：L"）。选择
Selection.Copy
Selection.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks _
：= False，Transpose：= False
Range（" M2"）。选择
结束子
你好
问候
HI,
I tried kind recording Macro for the SUMIFS, however, it's taking very very long time to run the report, just to summarize, there are total three macros two macros to lookup the values based on the specific criteria in a table (From Magento and OMS workbooks) and the 3rd Marco to run the reconciliation.
While running the 3rd Macro the excel is almost getting stuck. I have data which goes more than 200,000 lines or more
Is there any suggestions to improve the code.
kindly advise.
regards
<g class="gr_ gr_43 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="43" id="43">aleem</g>
Option Explicit
Sub Reco()
'
' Reco Macro
'
'
Application.ScreenUpdating = False
Windows("Magento.xlsx").Activate
Range("C:C,J:J,N:N").Select
Range("N1").Activate
Range("C:C,J:J,N:N,AI:AI").Select
Range("AI1").Activate
Selection.Copy
Windows("Reconciliation File.xlsm").Activate
Sheets("Magento").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A6").Select
Windows("OMS.xlsx").Activate
ActiveWindow.ScrollColumn = 19
Range("D:D,E:E,U:U,X:X").Select
Range("X1").Activate
Selection.Copy
Windows("Reconciliation File.xlsm").Activate
Sheets("OMS").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
Sheets("Magento").Select
Columns("A:B").Select
Selection.Copy
Sheets("Reconciliation").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("OMS").Select
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Reconciliation").Select
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:B").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$B$100000").RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlNo
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Magento!C4,Magento!C2,Reconciliation!RC2,Magento!C3,Reconciliation!R2C)"
Range("C3").Select
Selection.Copy
Range("C3:G3").Select
ActiveSheet.Paste
Range("H3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUMIFS(OMS!C4,OMS!C2,RC2,OMS!C3,R2C)"
Range("H3").Select
Selection.Copy
Range("H3:L3").Select
ActiveSheet.Paste
Range("C3:L3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B3").Select
Selection.End(xlDown).Select
Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll down:=-140
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:L").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M2").Select
End Sub
Hi aleemATG,
I can see in code that on so many lines unnecessary steps were recorded, Like just selecting the range again and again.
Or selecting one range then select any other range then again select any other range.
So executing this repetitive steps with 2,00,000 rows will consume lots of time.
Also in recorded macro, Not all the steps get recorded. Excel will not record some steps which you need to modify it later as per your requirement.
So I suggest you to try to understand your recorded macro and try to developed your own macro based on that.
It will help you to improve the performance.
Reference:
WorksheetFunction.SumIfs Method (Excel)
Object model (Excel VBA reference)
Regards
这篇关于Sumifs花了很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
