Prev: Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page
Next: Macro to copy subtotaled data
From: Frank on 31 May 2010 13:56
Hi Helmut:

This is really odd and something's wrong in Excel (I'm using 2003,
maybe this bug as been fixed in later versions)

i = 1
For Each cell In Range("my_range")
i = i + 1
'insert new sheet
Worksheets.Add(After:=Sheet1).Name = "sheet" & i
Columns("C:C").Value = Columns("D:D").Value
Next

Of course, the above code is useless but it proves that there is an
issue with columns.value = columns.value

You will see it fail when i = 17, therefore on the 16th sheet.

Regards,
From: GS on 31 May 2010 14:25
Frank used his keyboard to write :
> Hi Helmut:
>
> This is really odd and something's wrong in Excel (I'm using 2003,
> maybe this bug as been fixed in later versions)
>
> i = 1
> For Each cell In Range("my_range")
> i = i + 1
> 'insert new sheet
> Worksheets.Add(After:=Sheet1).Name = "sheet" & i
> Columns("C:C").Value = Columns("D:D").Value
> Next
>
> Of course, the above code is useless but it proves that there is an
> issue with columns.value = columns.value
>
> You will see it fail when i = 17, therefore on the 16th sheet.
>
> Regards,

You need to change from using Columns() to using Range().

Example:
Range("C:C") = Range("D:D")

You don't have to specify value unless the target range is to receive
the product of a formula in the source range. So...

Range("C:C") = Range("D:D").Value
where the cells in Range("D:D") contain a formula.

HTH
Garry

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Frank on 31 May 2010 15:11
Hi Garry:

Using Range("C:C") = Range("D:D").Value does not work either.

It stops at the 16th sheet as well.

From: GS on 31 May 2010 15:37
Frank wrote :
> Hi Garry:
>
> Using Range("C:C") = Range("D:D").Value does not work either.
>
> It stops at the 16th sheet as well.

In this case, I suggest you post your code in its entirety so we can
see how to better help you fix it.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Helmut Meukel on 31 May 2010 18:05
Hi Frank,

I got curious an made some tests:
(I numered the different code passages for each test, see comments)

In a new workbook with 1 sheet:
Sub Test()
Dim ws As Worksheet
i = 1
Do
i = i + 1
'insert new sheet
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Sheet" & i
'let's assign the values of one column to another
'1.Test
'1'ws.Columns("C:C").Value = ws.Columns("D:D").Value
'2.Test: Range instead Column
'2'ws.Range("C:C").Value = ws.Range("D:D").Value
Loop Until i = 30

'3. to 6.Test: 1 + 2 (above) commented out, added second loop for the
copying
'3.Test: just a second loop
'4.+ 5.Test: trying to avoid the Out of Memory and 1004 errors
'by setting to Nothing and adding doevents (desperate now)
'6.Test: instead of iterating the sheets perform the same operation multible
times
'for the same sheet

Set ws = Nothing
i = 1
Do
i = i + 1
'3'Set ws = Worksheets("Sheet" & i)
'6'Set ws = Worksheets("Sheet" & 1)
ws.Columns("C:C").Value = ws.Columns("D:D").Value
'ws.Range("C:C").Value = ws.Range("D:D").Value
'4'Set ws = Nothing
'5'DoEvents
Loop Until i = 30

End Sub

Guess what happened: the code always stopped after 16 successful
passes with an Out of Memory error message followed by an error 1004
The last test (number 6) run without any error!
Did the tests on an old machine with Excel97 and WinNT4 and
plenty of virtual memory.

Helmut.

"Frank" <san.francisco.scrabble(a)gmail.com> schrieb im Newsbeitrag
news:0e9c0de5-12de-42fb-93a1-cae1179ff0fa(a)z15g2000prh.googlegroups.com...
> Hi Helmut:
>
> This is really odd and something's wrong in Excel (I'm using 2003,
> maybe this bug as been fixed in later versions)
>
> i = 1
> For Each cell In Range("my_range")
> i = i + 1
> 'insert new sheet
> Worksheets.Add(After:=Sheet1).Name = "sheet" & i
> Columns("C:C").Value = Columns("D:D").Value
> Next
>
> Of course, the above code is useless but it proves that there is an
> issue with columns.value = columns.value
>
> You will see it fail when i = 17, therefore on the 16th sheet.
>
> Regards,


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page
Next: Macro to copy subtotaled data