none
range out of memory error 7 RRS feed

  • Question

  • Hi!

    My goal is to create a universal macro that fills up cells in any workbook with information from another workbook using "vlookup". For now, I am only trying to make the macro work for the first value I want to use the formula for. Both EXCEL files have a standard design that never changes.

    1. I manually open the file I want to modify ("03-2015 MI NB & CBP Final.xlsx")

    2. I open the file that is going to be my "range" in the vlookup formula

    3. I go back to the "BookToModify" and select A1 to apply the formula

    4. when I execute the macro I get a "Out of memory error 7" message

    5. I need your help!!!!

    Here is my code and thanks for any help!!

    Sub FilEmpInfo()
    '
    ' FilEmpInfo Macro
    '

    '
        Dim BookToModify As String
        Dim EmpLog As String
        Dim EmpSheet As String
        Dim EmpRange As Range
       
        BookToModify = ActiveWorkbook.Name
       
        ChDir _
            "\\A0136-fil0100-s\Team136CHWRComp\2015\2015 Employee Info\2015 Final Employee Log"
        Workbooks.Open Filename:= _
            "\\A0136-fil0100-s\Team136CHWRComp\2015\2015 Employee Info\2015 Final Employee Log\03-2015 Customer Assurance Emp Log.xlsx"
        Cells.Select
        ActiveWindow.WindowState = xlNormal
       
        EmpLog = ActiveWorkbook.Name
        EmpSheet = ActiveSheet.Name
        Set EmpRange = Selection


    '    Windows("03-2015 MI NB & CBP Final.xlsx").Activate
        Windows(BookToModify).Activate
      
       
        Range("A2").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[10]," & EmpRange & ",3,FALSE)"
    End Sub

    Thursday, April 9, 2015 7:12 PM

Answers

  • While using a range in VLOOKUP, it is good to go with Activecell.Formula, rather than using FormulaR1C1. Try with just Activecell.Formula in VLOOKUP as in this article.

    http://officetricks.com/how-to-do-vlookup/

    Whenever I use a Range (for example "A1:B2") within formulaR1C1 from within macro, the range get converted to 'A1':'B2'. i.e., a single quote gets in giving error. So, try the alternate solution. It might work.

    Monday, April 13, 2015 2:04 AM
  • Re:  "out of memory"

    Microsoft increased the number of rows and columns in a worksheet starting with xl2007.
    Those worksheets have 1,048,576 rows x 16,384 columns; but they didn't say you could use all of them. (the marketing dept. has control)
    EmpRange has over 17 billion cells in it and Excel is unable to work with that many cells.

    Try changing...
    Set EmpRange = Selection
    To...
    Set EmpRange = EmpSheet.UsedRange
    And...
    Remove "Cells.Select"

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, April 10, 2015 1:03 PM
  • Hi Malgoro,

    In addition the suggestion of James and Kumarapush, to refer to range out of the active worksheet, we need to use the external reference. In this case, we can change the code like below:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[10]," & EmpRange.Address(ReferenceStyle:=xlR1C1, External:=True) & ",3,FALSE)"
    You can get more detail about range address topic from link below:
    Range.Address Property

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 13, 2015 6:21 AM
    Moderator
  • Re:  "Invalid Qualifier"

    That's what happens when you follow my directions. <grin>
    This should work...

    Instead of...
      Set EmpRange = EmpSheet.UsedRange
    Use...
      Set EmpRange = Worksheets(EmpSheet).UsedRange

    Also, review the two posts by Fei Xue and Kumarapush
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, April 13, 2015 2:17 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, April 10, 2015 5:51 AM
  • Re:  "out of memory"

    Microsoft increased the number of rows and columns in a worksheet starting with xl2007.
    Those worksheets have 1,048,576 rows x 16,384 columns; but they didn't say you could use all of them. (the marketing dept. has control)
    EmpRange has over 17 billion cells in it and Excel is unable to work with that many cells.

    Try changing...
    Set EmpRange = Selection
    To...
    Set EmpRange = EmpSheet.UsedRange
    And...
    Remove "Cells.Select"

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, April 10, 2015 1:03 PM
  • While using a range in VLOOKUP, it is good to go with Activecell.Formula, rather than using FormulaR1C1. Try with just Activecell.Formula in VLOOKUP as in this article.

    http://officetricks.com/how-to-do-vlookup/

    Whenever I use a Range (for example "A1:B2") within formulaR1C1 from within macro, the range get converted to 'A1':'B2'. i.e., a single quote gets in giving error. So, try the alternate solution. It might work.

    Monday, April 13, 2015 2:04 AM
  • Hi Malgoro,

    In addition the suggestion of James and Kumarapush, to refer to range out of the active worksheet, we need to use the external reference. In this case, we can change the code like below:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[10]," & EmpRange.Address(ReferenceStyle:=xlR1C1, External:=True) & ",3,FALSE)"
    You can get more detail about range address topic from link below:
    Range.Address Property

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 13, 2015 6:21 AM
    Moderator
  • Thanks Jim!

    After following your suggestion, I'm getting an "invalid qualifier" compilation message...

    I tried defining it differently, using another variable, parenthesis, quotation marks, but nothing helps.

    any other suggestion?

    thanks!

    Monday, April 13, 2015 1:16 PM
  • Re:  "Invalid Qualifier"

    That's what happens when you follow my directions. <grin>
    This should work...

    Instead of...
      Set EmpRange = EmpSheet.UsedRange
    Use...
      Set EmpRange = Worksheets(EmpSheet).UsedRange

    Also, review the two posts by Fei Xue and Kumarapush
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, April 13, 2015 2:17 PM