xl2000 VBA upgrade Woes 1

[sjpistolesi]

In xl97, I happily plugged in text into a cell and got the correct result.

Now, in xl2000, the use of either the .Value or the .Formula][\b] property won't work after I've placed a statement in them.

Example [per Microsoft help file]:
Worksheets("Sheet1&quot.Range("A1&quot.Formula = "=$A$4+$A$10"

FYI, the target cell format is set to number[\b].

Has anyone else had this problem??? Have you found the solution to making it work??

Thanks, Sandy

 

[cheerio]

I just posted your code example into an Excel 2000 workbook and it executed without problem putting the formula into the correct cell. The only issues I have had are with backwards compatibility where I have used a function in Excel 2000 that does not exist in earlier versions.

There are various reasons why it might not work but these are unlikely to be related to the upgrade itself. For example, the process would fail if the worksheet were protected. In addition, Excel 2000 has stronger macro virus protection - if security is at the highest level no macros will run unless they have been digitally signed by a trusted source.

What error message do you get?

Do you get the problem when you try to execute the code example in a completely new blank workbook?

Ken

 

[sjpistolesi]

Ken ... a bit more the story, sorry to confuse you.

First, to answer your question ---- The symptom is not an error message, but rather a non-active response ... equation of formula in the major totals cell, referencing the shorter subtotal strings, but no activity occurs in the major total cells.

To be honest, I'd not "visited" these spreadsheets until I am now facing an upgrade. The users were happily working with them for several years. The purpose is to provide them subtotals and final total that change the instant they change the value of an item cell, and immediately see the results such an action would take overall.

Thinking back, I now recall that there are far more cell addresses in the formula than I described originally.

I have multiple projects with subtotals. As I step down through them, I am gathering their subtotal cell addresses into a string for the overall total. (I suspect the strings are too long as a result.)

I also recall that, last time, I had to "fool" Excel by "parking" shorter strings of the [project subtotals'] cell addresses being referenced off to the right, out of the clients' view of course, to avoid running into a "length of string" problem with the final total's formula string. (Yup, Excel has its limitations.) Here's the full instruction that results:

Worksheets("Sheet1&quot.Range("B315&quot.Formula = "=SUM($B$18, $B$43, $B$110, $B$137, $B$146, $B$162, $B$174, $B$179, $B$183, $B$186, $B$193, $B$204, $B$212, $B$216, $B$221, $B226, $B$231, $B$236, $B$242, $B$246, $B$265, $B$269, $B$285, $B$190, $B$297, $B$313)"

So, now, I'm fiddling with parsing the subtotal cell string again, and debugging. I've tried to [neatly] gather the subtotal cell addresses, vertically aligned and hiding the rows containing the shortened strings ... but the major total cell will not work with those cell addresses after they are hidden and then referenced by the major total cells. I'm guessing that I might have to use the original method of placing the subtotal strings off to the right again, out of the user view again and not "hiding" them.

But, thanks anyway for taking the time to experiment with me.

Regards, Sandy

 

[cheerio]

Have you investigated Data, Subtotals? This is effectively a wizard for using the subtotals function. Once you understand how it works you can do it from code yourself.

The clever thing abount the subtotals function is that if it is supplied with a range that itself contains subtotals produced with the function it will sum ignoring the existing subtotal amounts so you can get grand totals without having the long lists you appear to need.

An example is shown below which could be posted into a worksheet at cell A1

dept	    value
A	    12
A	    13
A	    14
A Total	    =SUBTOTAL(9,B2:B4)
B	    5000
B	    6000
B	    9000
B Total	    =SUBTOTAL(9,B6:B8)
C	    100
C	    200
C	    300
C Total     =SUBTOTAL(9,B10:B12)
Grand Total =SUBTOTAL(9,B2:B12)

Ken