* Resolved * Excel is crap... and still is

Right... it is, and unfortunately I'm stuck with it instead of Access (which other people think is crap - personally I don't).

Anyway the scenario:

We get reports of data, 30k lines easy, from a oracle db in an Excel unformatted file (also has a stupid pivot table on the front which actually restricted data output in certain cases).

Now for a year the team I'm on has reformatted the spreadsheet to make the information they want appear the way they want it.

Being bored and wanting to try to keep my programming skills alive, I thought I'd try my hand at making a flexible form to take in the spreadsheet of any report from oracle and reformat it onto a new sheet with all the data pivotted/crosstabed and neat, with colours and borders.

Mostly its gone ok.

The Problem
I was using an arry to put to the sheet over a selected range.

VB Code:

set rng = shtDest.range......
rng = aryOP()

The range is fine, the array is fine. The array to sheet was not. errored in the usual way - 1001 Excel cannot cope! ok ok. Application or object errored.

Now I've pinned it down to two strings which are functions. They look fine in the array, but when transferred to Excel, they either don't go (the error) or go and are changed (one went across but the cells referred to in the function didn't) or it goes in fine, on one cell.
The other cell has:
- not gone in at all - the error msg
- caused a circular reference and not gone in.

The functions

Code:

---

=H2+I2+J2+L2+M2+N2+O2+P2+Q2+R2+H2+I2+J2+L2+M2+N2+O2+P2+Q2+R2
=if(or(H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,P2=0,Q2=0,R2=0,S2=0),"Hide","Show")

---

<edit>
The speech mark in bold was mising causing an error 1004
</edit>

Spot anything wrong with them??
Any handy hints (apart from not bothering with excel ever again - possibly MS and just take up a life as a gardener or builder) ??


Vince
<edit>
I still think excel is crap...
</edit>

is this a typo in your post, or is it in the data?

Quote:

---

),"Hide","Show")

---

<edit: spotted your edit!>


The other line looks fine (odd as it has the same cells twice, and could just be a Sum function instead), but I'm guessing that it is the one causing the circular reference error.. it depends on where it is being placed, and what formulas are in the other cells (and the ones that their formulas refer to).