Prev: Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page
Next: Macro to copy subtotaled data
From: Barb Reinhardt on 20 May 2010 12:31
What is the rest of your code?



"Frank" wrote:

> I cannot figure this one out.
>
> I have a simple macro which adds 40 new worksheets and retrieves data
> on each of these new sheets in column C.
>
> Here is where the code fails
>
> Columns("C:C").Value = Columns("D:D").Value
>
> It works for 15 sheets but on the 16th, I get the following error
> message:
>
> Run-time error '1004':
> Application-defined or object-defined error
>
> Any ideas?
> .
>
From: Frank on 20 May 2010 12:57
the original code was

'remove before and after blanks via TRIM function
Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FormulaR1C1 =
"=TRIM(RC[-1])"
Columns("C:C").Value = Columns("D:D").Value

I ran the code without the Columns("C:C").Value = Columns("D:D").Value
and it worked fine
but when included, if fails at the 16th sheet.

I know I could do cell.value = trim(cell).value but I
find .FormulaR1C1 to work faster.
From: GS on 20 May 2010 20:55
Frank wrote :
> the original code was
>
> 'remove before and after blanks via TRIM function
> Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FormulaR1C1 =
> "=TRIM(RC[-1])"
> Columns("C:C").Value = Columns("D:D").Value
>
> I ran the code without the Columns("C:C").Value = Columns("D:D").Value
> and it worked fine
> but when included, if fails at the 16th sheet.
>
> I know I could do cell.value = trim(cell).value but I
> find .FormulaR1C1 to work faster.

Well, having more info about what you're trying to do certainly helps.
In this case I suggest to just trim the values in "C:C" rather than
enter a formula in "D:D" to do so and then copy the result back to
"C:C". Doesn't make sense to go to all the trouble when you could use
the VBA Trim() function on the "C:C" cells.

Here's an example:

Sub TrimLeftRightSpaces()
Dim c As Range, lLastRow As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count
For Each c In Range(Cells(1, "C"), Cells(lLastRow, "C"))
If Not c = "" Then c.Value = Trim$(c.Value)
Next
End Sub

HTH
Garry

--
Garry

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


From: Frank on 21 May 2010 11:34
Hi Garry:

I thought of that but I find the r1c1 option faster.

I opted for the range.value = range.value and it works.

But it still puzzles me that the column.value = column.value works
fine for 15 sheets and yet fails on the 16th.

Regards,
From: Helmut Meukel on 29 May 2010 07:34
"Frank" <san.francisco.scrabble(a)gmail.com> schrieb im Newsbeitrag
news:3f503742-914a-42cc-9dbc-8da9743927ec(a)j36g2000prj.googlegroups.com...
> Hi Garry:
>
> I thought of that but I find the r1c1 option faster.
>
> I opted for the range.value = range.value and it works.
>
> But it still puzzles me that the column.value = column.value works
> fine for 15 sheets and yet fails on the 16th.
>
> Regards,


Did you try to change the order of the sheets?
What I mean is:
Is it always the 16th sheet regardless which sheet
is the 16th?
Or is it a particular sheet?
I assume it's a particular sheet.
Did you step through all cells of column C and look
for something unusual?
Or is there something in column D far below your last row
which may cause the problem?
I faintly remember I had once - 15 or more years ago -
a problem which I solved by selecting all rows below my
last row and deleting the selection.

Helmut.


 |  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