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

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

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

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

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

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

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

Out of Memory Error 7 While assigning named range value
 

Thanks Mark for your help.....and Time.

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