Results 1 to 7 of 7
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Out of Memory Error

    I'm writing the below snippet to try and search through every other column of a range and find cells that aren't blank. I want to fill the offset (0,1) with "kg". Can someone help me re-write this? I can't find any literature online about it. It keeps giving me either error type mismatch or error out of memory. I have made sure that all my variables and recordsets (I'm writing a very long code to export different queries with formatting) are set to nothing so I'm not sure what the out of memory error could be


    Code:
    Dim i As Long
    Dim wks As Excel.Worksheet
    
    For i = 3 To 22 Step 2    
    
    With wks.Range(wks.Cells(Lrow + 9, i), "C" & Lrow + 19)
            If wks.Cells.Value <> "" Then
                wks.Cells.Offset(0, 1).Value = "kg"
            End If
        End With
    Next i
    IF I change the code to this, then nothing happens. The spreadsheet exports and there are no 'kg' markers.
    Code:
    For i = 3 To 22 Step 2    
    With wks.Range(wks.Cells(Lrow + 9, i), "C" & Lrow2)
            If Value <> "" Then
                .Offset(0, 1).Value = "kg"
            End If
        End With
    Next i

    Last edited by lccrews; 11-06-2018 at 11:09 AM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    what is : Lrow defined as? Long? , its not shown if it is defined.
    what is the value?


  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post an example of the Excel worksheet you want to edit?

    ------------------------------------
    I think that part of the problem is that you are mixing how you reference cells.
    Code:
    With wks.Range(wks.Cells(Lrow + 9, i), "C" & Lrow + 19)
    In the first part of the RANGE object , you use Cells notation and the second part you use "A1" notation. I don't believe you can mix these styles for one Range reference..

  4. #4
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks for the response ranman and ssanfu.

    ranman,
    Lrow is determined as: Lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row. The value is dynamic depending on how many records are pulled from the crosstab.

    ssanfu,
    Below are screenshot of one output. This shows what I want it to look like after I've finished formatting. Now the crosstab at the bottom (marked by header 'raw material') shows a number of ingredients responses. I need a way to loop through every other column and search for <>"" cells and .offset(0, 1).value = "kg"
    Attached Thumbnails Attached Thumbnails 2.jpg  

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post the SS so I don't have to try and create one?

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Here is a copy of my SS. If you want, tomorrow when I get into work, I'll post the entire code that I'm using. I left it out of this post because I didn't think it was relevant. It may be though?
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, try this.

    You will need to change the reference to Excel from 14 (2010) to 16? (2016).
    You will need to change the location (path & filename) of the Excel workbook in the code for the button click event.

    The code assumes:
    that the text "RawMaterial" will be somewhere in column "B" (but not the first row),
    that the cells you want to change begin in the row after "RawMaterial" and
    that the code will stop at the first Empty cell below "RawMaterial".
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. memory error on filed change
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 10-30-2017, 02:03 PM
  2. Out of memory error
    By BRZ-Ryan in forum Forms
    Replies: 14
    Last Post: 12-02-2013, 09:24 PM
  3. Impossible Access error (Out of memory)
    By guydols in forum Programming
    Replies: 8
    Last Post: 11-29-2012, 09:52 PM
  4. Replies: 9
    Last Post: 11-09-2011, 11:34 AM
  5. disk space or memory error
    By Cmitchell in forum Database Design
    Replies: 1
    Last Post: 04-04-2011, 03:36 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums