Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Hi All,
I am new to VBA programming and I am getting Out of Memory Error 7 While I assign text to a named cell like here the text contained in the variable is huge around 8 pages. I would really appreciate if some one can tell me an alternative way to do. Worksheets("worksheetname").Range("named").Value = variable Thanks in advance. Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
How large is your named range (how many cells)?
The code you have setup below will assign whatever the value of "variable" to your named range. If your named range is larger than ONE cell, then this data is repeated to each cell in your named range. Is this what you want? If you have a large volume of information in your variable and are assigning it to any one particular cell, that may be the problem. A single cell in Excel can only hold a certain amount of data. If you were to exceed that maximum, I could see you getting that error. If you can provide more detail for what your total goal is, I may be able to help you out more. Mark "smartnhandsome" wrote in message ... Hi All, I am new to VBA programming and I am getting Out of Memory Error 7 While I assign text to a named cell like here the text contained in the variable is huge around 8 pages. I would really appreciate if some one can tell me an alternative way to do. Worksheets("worksheetname").Range("named").Value = variable Thanks in advance. Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Thanks Mark,
You are correct the named range here is just a single cell, and I am retrieving data from Lotus notes Database to be assigned to a variable and then variable's value is assigned to the named cell(single cell B16). Worksheets("worksheetname").Range("named").Value = variable In one particular case the data that is retrieved from the db for this variable is 8 pages long when pasted into a word document. Is there any turn around to put all that text into one cell ?. Your help is really appreciated. Thanks again. Please let me know. Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Here are the facts...
The maximum amount of characters allowed in ONE cell are 32,767 (of which only 1,024 can be viewed from the cell). If you want to view all 32,767, you will have to do it from the formula bar. Other than that... I would recommend breaking up the data into more than one cell. Mark "smartnhandsome" wrote in message ... Thanks Mark, You are correct the named range here is just a single cell, and I am retrieving data from Lotus notes Database to be assigned to a variable and then variable's value is assigned to the named cell(single cell B16). Worksheets("worksheetname").Range("named").Value = variable In one particular case the data that is retrieved from the db for this variable is 8 pages long when pasted into a word document. Is there any turn around to put all that text into one cell ?. Your help is really appreciated. Thanks again. Please let me know. Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Thanks Mark,
I just noticed that the text that I was trying to put into a single cell was around 17,000 + characters and this is when I was getting Out of Memory Error 7, this is less than the maximum limit of 32,767 characters. Is there any particular reason I was getting this error well below the max value. Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Maybe you can break the named range into smaller chunks and assign the value to
each of those chunks one at a time. smartnhandsome wrote: Hi All, I am new to VBA programming and I am getting Out of Memory Error 7 While I assign text to a named cell like here the text contained in the variable is huge around 8 pages. I would really appreciate if some one can tell me an alternative way to do. Worksheets("worksheetname").Range("named").Value = variable Thanks in advance. Regards -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
With that out of the way...
Take a look at the following search results and see if anything may fit your problem: http://www.google.com/search?hl=en&r...memory+error+7 After you have exhausted this resource, check back and see if it is still a problem.. Mark "smartnhandsome" wrote in message ... Thanks Mark, I just noticed that the text that I was trying to put into a single cell was around 17,000 + characters and this is when I was getting Out of Memory Error 7, this is less than the maximum limit of 32,767 characters. Is there any particular reason I was getting this error well below the max value. Regards |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
Thanks Mark for your help.....and Time.
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory Error 7 While assigning named range value
I trying all the things I could find, I finally was able to get rid of
the problem by splitting the long string into smaller strings and putting them into another sheet and later putting those back into the main named range. Dim r As Range Set r = Worksheets("rec").Range("A1") Do While Not r = "" r.ClearContents Set r = r.Offset(1, 0) Loop Set r = Nothing If Len(Terms) 5000 Then copyRange (Terms) Else Worksheets("Input").Range("Terms") = Terms End If Function copyRange(textToCopy As String) Dim tempString As String Dim i As Integer Dim j As Integer For i = 1 To (Len(textToCopy) / 1024 + 1) j = (i * 1025) Worksheets("ref_rec").Cells(i, 1).Value = Mid(textToCopy, j - 1024, 1024) Next i Dim targetCell As Range Dim r As Range Set r = Worksheets("rec").Range("A1") Set targetCell = Worksheets("Input").Range("Terms") targetCell.Value = r.Value Set r = Nothing End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning named range to a 2-dimensional array | Excel Programming | |||
ASSIGNING THE VALUE TO A NAMED RANGE GIVING OBJECT DEFINED ERROR | Excel Programming | |||
Extracting unique entries and assigning it to a named range | Excel Discussion (Misc queries) | |||
Extracting unique entries and assigning it to a named range | Excel Programming | |||
Runtime error 91 when assigning range | Excel Programming |