Columns("C:C").Value = Columns("D:D").Value not working after addi

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 18:20

---

Hi Garry:

You can try the code I had posted earlier:

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

While the code does not do anything, it just proves my piont that
there is a bug in Excel.

And here is another bug:

I'm developping an application where people can optimize data given a
slew of options.

Each execution requires that an existing sheet with data must be
duplicated in order performe calculations,

I discovered that using Sheets("test").Copy After:=Sheets("test") will
fail after running the execution after about 100 times (It depends how
much data is on Sheets("test").

It forces the user to close Excel. Once reopened, it works again until
the nex time.

So I have used the copy/paste method and it works fine.

Maybe they are solutions to those issues but I am not a programmer by
profession.

From: GS on 31 May 2010 20:19

---

Frank was thinking very hard :
> Hi Garry:
>
> You can try the code I had posted earlier:
>
> 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
>
> While the code does not do anything, it just proves my piont that
> there is a bug in Excel.
>
> And here is another bug:
>
> I'm developping an application where people can optimize data given a
> slew of options.
>
> Each execution requires that an existing sheet with data must be
> duplicated in order performe calculations,
>
> I discovered that using Sheets("test").Copy After:=Sheets("test") will
> fail after running the execution after about 100 times (It depends how
> much data is on Sheets("test").
>
> It forces the user to close Excel. Once reopened, it works again until
> the nex time.
>
> So I have used the copy/paste method and it works fine.
>
> Maybe they are solutions to those issues but I am not a programmer by
> profession.

Hi Frank,

I still don't see where the contents of Columns("D") gets onto the
sheet so as to copy the value to Columns("C"), so I entered this
formula into several rows of Columns("D"):
=Row()*2

Then, in the Immediate Window I entered:
Columns("C").Value=Columns("D").Value

and the values appeared as expected.

I ran your code to add worksheets and populate the data as you are
doing. I got a memory error as did Helmut. (Probably because I had 8
apps running at the time, and so resources were heavily taxed) Just to
verify my hardware, I did this on a XP SP3 machine running 1.6ghz
processors (dual) with 2gb RAM. So nothing special there! I left all as
was to continue testing with the current tax on resources left in
place.

Next, I opened a new workbook with one sheet (my usual default #sheets)
and entered the following in the Immediate Window:
ActiveWorkbook.Sheets.Add After:=Sheets(1), Count:=40

and the 40 sheets appeared instantaneously.

Next, I grouped all the sheets from 2-41 and entered the following in
the Immediate Window:
UpdateSelectedSheets

which ran the following procedure:

Sub UpdateSelectedSheets()
Dim wks As Worksheet, vData() As Variant, c As Range
Dim lRows As Long, r As Long
With ActiveWorkbook.Sheets("Sheet1")
lRows = .UsedRange.Rows.Count
ReDim vData(lRows)
For r = LBound(vData) To UBound(vData)
vData(r) = .Cells(r + 1, "D").value
Next
End With
For Each wks In ActiveWindow.SelectedSheets
wks.Range("C1:C" & CStr(lRows)).value = vData
Next
End Sub

Just as instananeously as the 40 sheets were added, they were all
successfully updated with the data.

I repeated this by adding another 40 sheets, grouping all 80, and
running the UpdateSelectedSheets procedure again. I got the same result
without error!

Not sure this would work for you but I strongly recommend you consider
a different approach to adding AND updating the 40 sheets one at a
time. Setting object refs and maintaining variables adds to the
resources load, and so anything done in quantity should be done as much
as possible without over-taxing available resources.

If you google here in this NG you'll find code here to group sheets. If
not found then post back and I'll provide code.

In case we don't resolve this before M$ shuts this NG down, I'll try
finding this thread in the new location. (Assuming the posts are
archived there) Otherwise, we'll have to figure something out. I
already tried the NNTPbridge deal but when I signed in I was informed
that my credentials were already in use by another member. IOW, it
didn't recognize me as me! Kinda put me off wanting to persist further.

regards,

--
Garry

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

From: Frank on 1 Jun 2010 11:56

---

Hi Garry:

I’ll give you the background as to why I used so many sheets:

I am not a programmer by trade but I’m able to create some decent VBA
routine.

On each sheet (variable i) that is being created, I get external data
with two columns: Column A is for date and Column B is for data.

I then need to get further external query to adjust the data. I
previously used the r1c1 method and converted the formula using
Columns(“C:C”).value = Columns(“D:D”).value and discovered if failed
at the sixteenth sheet. I found a fix around it but still cannot
understand why it fails (hence the useless code I previously posted
and which apparently gives you an error as well.)

One all adjustments are made, I am left with two columns (A for date
and B for data)

I name the used ranged (source + i). The number of rows vary and not
all dates are the same (sheet2 might have 1/1/2000 while sheet3 might
not have that date).

Once I have my 40 sheets, I combine them via a pivot table and I have
all my data according to dates. Basically, I created a database.

I had a previous version using only one sheet but I was limited by the
number of rows (65536) to create the pivot table.

They are maybe better ways to code this but as I mentioned before, I
am not a programmer by trade.

I’ve tried to duplicate the Sheets("test").Copy After:=Sheets("test")
error I get but was not able to. I also found a work around and my
posting was more of a curiosity.

Regards,

From: GS on 1 Jun 2010 14:17

---

Frank brought next idea :
> Hi Garry:
>
> I'll give you the background as to why I used so many sheets:
>
> I am not a programmer by trade but I'm able to create some decent VBA
> routine.
>
> On each sheet (variable i) that is being created, I get external data
> with two columns: Column A is for date and Column B is for data.
>
> I then need to get further external query to adjust the data. I
> previously used the r1c1 method and converted the formula using
> Columns(“C:C”).value = Columns(“D:D”).value and discovered if failed
> at the sixteenth sheet. I found a fix around it but still cannot
> understand why it fails (hence the useless code I previously posted
> and which apparently gives you an error as well.)
>
> One all adjustments are made, I am left with two columns (A for date
> and B for data)
>
> I name the used ranged (source + i). The number of rows vary and not
> all dates are the same (sheet2 might have 1/1/2000 while sheet3 might
> not have that date).
>
> Once I have my 40 sheets, I combine them via a pivot table and I have
> all my data according to dates. Basically, I created a database.
>
> I had a previous version using only one sheet but I was limited by the
> number of rows (65536) to create the pivot table.
>
> They are maybe better ways to code this but as I mentioned before, I
> am not a programmer by trade.
>
> I've tried to duplicate the Sheets("test").Copy After:=Sheets("test")
> error I get but was not able to. I also found a work around and my
> posting was more of a curiosity.
>
> Regards,

Hi Frank,

Thanks for the additional info. This confirms my suspicions about what
it was that you were trying to do. So essentially, it appears you are
parsing out data from a data source (querying a database?) onto
separate sheets so data is grouped according to some logical criteria.

Using Excel as a database is not the best choice, but it does have
valid purpose within its limitations. One major limitation for large
numbers of records is the 65,536 row limit on XL11 and earlier. (I've
heard XL12 has a row limit of about 1 million) Excel and VBA are
storing that data in mem until it's freed or the variables are
destroyed. It all adds up rather quickly when we're working with lots
of objects and large amounts of data.

One thing that stands out to me is that you are trying to work with
entire columns of data. Nothing wrong with that per se, but it taxes
resources heavily when you do lots of it within the same procedure.
Each time you set/load variables with new values it adds to the amount
of space reserved in memory each time you reset/reload. This mem is not
cleared until your procedure ends and/or all the variables/refs are
destroyed. It might prove a more reliable approach to put your
individual steps into separate procedures that are called from your
main procedure. For example, if you look at how I added the 40 sheets,
it used hardly any resources whatsoever because Excel did all the work
and so managed the mem for me. That works well within a single main
procedure. Your approach would work better in a separate procedure so
the mem is cleared when the proc ends.

It appears that your programming methodology also includes proceeding
one step at a time for one sheet at a time. Again, nothing wrong with
that in small quantity. What hurts is that uses more resources until
the file is saved because everything is still in memory. If I didn't
know better I'd think your method of programming involves heavy use of
the macro recorder, which normally generates extremely inefficient code
under the best of circumstances. I'm not saying that's how you work.
It's just that your approach in some ways mimics that same step-by-step
inefficiency.

Another observation is that you seem to be using more columns than
necessary to build the final data you end up with. If the end result is
in Columns("A:B"), and Columns("B") contains the resulting data from
all your other manipulations, then it seems like there's way lots more
work being done than needs to be given the source data is already
located in a database. Not trying to criticize or take away from your
accomplishment, ..just saying (as you stated already) there's probably
lots more efficient ways to do what your doing.

Anyway, I'm always glad to help! -It's available for the asking...

regards,

--
Garry

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

From: Frank on 1 Jun 2010 14:55

---

Hi Garry.

All criticisms are quite welcome.

I’m a finance guy by the way.

When I started using VBA 8 years ago, I was using the macro recorder
and then cleaning it.

As I got better with books, my skills improved. I build quite
sophisticated spreadsheet applications. My clients like them. One even
suggested I sell them but the quality of my programming skills is not
high enough to make it commercial.

See, when you write “Each time you set/load variables with new values
it adds to the amount
of space reserved in memory each time you reset/reload. This mem is
not
cleared until your procedure ends and/or all the variables/refs are
destroyed”, that just goes right over my head.

I do make use of running several procedures from the main “button” (my
worksheets involve buttons to execute the code”. I build of lot or
errhandler. Sometimes too many and I had to revert to on error goto 0
to find out about this column.value issue.

Again, thank for responding. I appreciate it.

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