Excel VBA内存错误If语句 [英] Excel VBA Out of Memory Error on If Statement

问题描述

我想在Excel中找出一个问题。我有一个内存不足错误，我怀疑这不是问题，但我不知道。

基本上，我试图制作一个宏，搜索对于电子表格中的5个选项卡中的列（该列可以在5中的任何一个中，并且更改很多），并且在找到它时，执行vlookup匹配以将列返回到主选项卡中的相应位置。这是我的下面的代码，似乎它应该工作，但我得到内存不足错误。当我去调试时突出显示的行是if语句中的第一个 Vrange = rB 行。

  Dim i As Integer 
 Dim r As Range 
'
 Dim wsMaster As Worksheet：设置wsMaster = Workbooks（LBImportMacroTemplate.xlsm）。Worksheets（工作表（B）
 Dim wsE As Worksheet：设置wsE =工作簿（LBImportMacroTemplate.xlsm）工作表（LBImportMacroTemplate.xlsm） 。工作表（L）
 Dim wsI As Worksheet：设置wsI =工作簿（LBImportMacroTemplate工作表（I）
 Dim wsT As Worksheet：设置wsT =工作簿（LBImportMacroTemplate.xlsm）。工作表（T）
'
 Dim rBHeading As范围：设置rBHeading = wsB.Range（A2：ZA2）
 Dim rEHeading As Range：Set rEHeading = wsE.Range（A2：ZA2）
 Dim rLHeading As Range：Set rLHeading = wsL .Range（A2：ZA2）
 Dim rIHeading As Range：设置rI标题= wsI.Range（A2：ZA2）
 Dim rTHeading As Range：Set rTHeading = wsT.Range（A2：ZA2）
'
 Dim rB As Range：Set rB = wsB.Range（A：ZA）
 Dim rE As Range：Set rE = wsE.Range（A：ZA）
 Dim rL As Range：Set rL = wsL.Range（ A：ZA）
 Dim rI As Range：Set rI = wsI.Range（A：ZA）
 Dim rT As Range：Set rT = wsT.Range（A：ZA）
'
 Dim mf_iA_TEXT As String：mf_iA_TEXT =iA
'
如果Application.CountIf（rBHeading，iA）= 1然后
 Vrange = rB 
 Mrange = rBHeading 
 ElseIf Application.CountIf（rEHeading，iA）= 1然后
 Vrange = rE 
 Mrange = rEHeading 
 ElseIf Application.CountIf（rLHeading，iA ）= 1然后
 Vrange = rL 
 Mrange = rLHeading 
 ElseIf Application.CountIf（rIHeading，iA）= 1然后
 Vrange = rI 
 Mrange = rIHeading 
 Else 
 Vrange = rT 
 Mrange = rTHeading 
 End If 
'
 Dim mf_iA As Variant：mf_iA = Application.Match（mf_iA_TEXT，Mrange， 0）
 '
 With ActiveSheet 
 lastrow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
 MsgBox lastrow 
 End with 
'
 For i = 2 To lastrow 
 wsMaster.Cells（i，2）= Application.VLookup（wsMaster.Cells（i，1），Vrange，mf_iA，0）
 Next i 
'
 End Sub 
  

我也尝试用case语句来完成这个，但我觉得像我上面的代码一样。如果可以让我知道这个代码是否愚蠢，或者如果我可以解决内存不足错误，我将非常感激。如果我可以让这个工作，我会复制这个过程与许多更多的列，万一重要。谢谢!!

解决方案

为了让您开始，前56行代码可以写成

  Dim v As Long，vWSs As Variant，Mrange As Range，Vrange As Range，mf_iA as long 
 Dim wsMaster As Worksheet：Set wsMaster =工作簿（LBTportMacroTemplate.xlsm）工作表（MasterTab）
 Dim mf_iA_TEXT As String：mf_iA_TEXT =iA
 
 vWSs = Array（B，E L，I，T）
与工作簿（LBImportMacroTemplate.xlsm）
 mf_iA = 0：Set Mrange = Nothing：Set Vrange = Nothing 
对于v = vWS）到UBound（vWSs）
如果CBool​​（Application.CountIf（.Sheets（vWSs（v））。Range（A2：ZA2），mf_iA_TEXT））然后
设置Mrange = .Sheets vWSs（v））范围（A2：ZA2）
设置Vrange = .Sheets（vWSs（v））。范围（A：ZA）
'下一行
 mf_iA = application.match（mf_iA_TEXT，Mr ange，0）
退出
结束如果
下一个v 
结束
 
如果mf_iA = 0，那么msgbox停在这里！ & mf_iA_TEXT&没有找到！
'假设Mrange和Vrange在这一点上不是什么
'，而mf_iA是mf_iA_TEXT 
'做某事的数字列索引号与他们
 
 
设置Mrange =没有
设置Vrange =没有
设置wsMaster =没有
  

这将带你到$ code的结尾If / ElseIf / End If 你可以继续处理，最后三个是完成之后，只需提醒手动将范围和工作簿对象设置为无。

I'm trying to figure out a problem in Excel. I am getting an Out of Memory error and I suspect that's not the problem but I don't really know.

Basically, I'm trying to make a macro that searches for a column within 5 tabs in a spreadsheet (the column can be in any of the 5 and it changes a lot) and when it finds it, does a vlookup match to return the column to the appropriate place in the master tab. Here is my code below which seems like it should work but I get the Out of Memory error. the line that is highlighted when I go to debug is the first Vrange = rB line in the if statement.

Dim i As Integer
Dim r As Range
'
Dim wsMaster As Worksheet: Set wsMaster = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("MasterTab")
Dim wsB As Worksheet: Set wsB = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("B")
Dim wsE As Worksheet: Set wsE = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("E")
Dim wsL As Worksheet: Set wsL = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("L")
Dim wsI As Worksheet: Set wsI = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("I")
Dim wsT As Worksheet: Set wsT = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("T")
'
Dim rBHeading As Range: Set rBHeading = wsB.Range("A2:ZA2")
Dim rEHeading As Range: Set rEHeading = wsE.Range("A2:ZA2")
Dim rLHeading As Range: Set rLHeading = wsL.Range("A2:ZA2")
Dim rIHeading As Range: Set rIHeading = wsI.Range("A2:ZA2")
Dim rTHeading As Range: Set rTHeading = wsT.Range("A2:ZA2")
'
Dim rB As Range: Set rB = wsB.Range("A:ZA")
Dim rE As Range: Set rE = wsE.Range("A:ZA")
Dim rL As Range: Set rL = wsL.Range("A:ZA")
Dim rI As Range: Set rI = wsI.Range("A:ZA")
Dim rT As Range: Set rT = wsT.Range("A:ZA")
'
Dim mf_iA_TEXT As String: mf_iA_TEXT = "iA"
'
If Application.CountIf(rBHeading, "iA") = 1 Then
    Vrange = rB
    Mrange = rBHeading
ElseIf Application.CountIf(rEHeading, "iA") = 1 Then
    Vrange = rE
    Mrange = rEHeading
ElseIf Application.CountIf(rLHeading, "iA") = 1 Then
    Vrange = rL
    Mrange = rLHeading
ElseIf Application.CountIf(rIHeading, "iA") = 1 Then
    Vrange = rI
    Mrange = rIHeading
Else
    Vrange = rT
    Mrange = rTHeading
End If
'
Dim mf_iA As Variant: mf_iA = Application.Match(mf_iA_TEXT, Mrange, 0)
'
With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastrow
End With
'
For i = 2 To lastrow
    wsMaster.Cells(i, 2) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_iA, 0)
Next i
'
End Sub

I also tried to accomplish this with a case statement but I felt like I got further with the above code. If you could please let me know if this code is dumb, or if I can solve the Out of Memory error, I would greatly appreciate it. If I can get this to work, I will be copying the process with many many more columns, in case that matters. Thanks!!

解决方案

To get you started, the first 56 lines of code could be written as,

    Dim v As Long, vWSs As Variant, Mrange As Range, Vrange As Range, mf_iA as long
    Dim wsMaster As Worksheet: Set wsMaster = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("MasterTab")
    Dim mf_iA_TEXT As String: mf_iA_TEXT = "iA"

    vWSs = Array("B", "E", "L", "I", "T")
    With Workbooks("LBImportMacroTemplate.xlsm")
        mf_iA = 0: Set Mrange = Nothing: Set Vrange = Nothing
        For v = LBound(vWSs) To UBound(vWSs)
            If CBool(Application.CountIf(.Sheets(vWSs(v)).Range("A2:ZA2"), mf_iA_TEXT)) Then
                Set Mrange = .Sheets(vWSs(v)).Range("A2:ZA2")
                Set Vrange = .Sheets(vWSs(v)).Range("A:ZA")
                ' added the column number assignment on the next line
                mf_iA = application.match(mf_iA_TEXT, Mrange, 0)
                Exit For
            End If
        Next v
    End With

    if mf_iA = 0 then msgbox "Stop here! " & mf_iA_TEXT & "not found!"
    'assumed that Mrange and Vrange are not nothing at this point
    ' and that mf_iA is the numerical column index number for mf_iA_TEXT
    'do something with them


    Set Mrange = Nothing
    Set Vrange = Nothing
    Set wsMaster = Nothing

That takes you to the end of the If/ElseIf/End If where you can continue processing. The last three are just reminders to manually set the ranges and workbook object to nothing after you are done with them.

这篇关于Excel VBA内存错误If语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！