My VBA creates an error in a formula

[ChrisNor]

Hi,

I've got an Excel workbook, with a built in macro that runs when you choose an entry in the two combo boxes.

IF you then try to change the value in B9:B15 or B22:B25 then an error message pops up and says that the sheet contains a formula with an error.

If you do not change both the combo boxes, then no such error shows up.

I've looked through formulas etc, but I can't seem to find the error. I hope that some of you might be able to give a guiding hand.

See attached file

[Wizz]

Hi ChrisNor,
I tested it, but no error msg appears. Could not replicate it.

[ChrisNor]

That is strange I've tested it on 3 different machines here, and all get the same error.

1. Pick something from the first combie box. (drop-down list)
2. Then pick something from the second drop-down list
3. When you have done this try to change the value in cell B12

Now you should get an error saying that one of the formulas contains a reference to a range that do not exist, or something. (this error message do not come when doing this before you have chosen from BOTH the drop-down lists)

[Andy Pope]

Your formula in H2 is returning a #REF as the row references is outside the array you are indexing in.

="<-- Systemvirkningsgrad "&INDEX('B.8 - Årsvirk.gr. oppvarming'!B4:B19,TEK!C2,0)

TEK!C2 contains 18 but the table only has 16 rows.

[ChrisNor]

Even if I delete the formula in H2, I still get the error

[Wizz]

I did it and still no error.
Each time I change a value in column B (after selecting different options in both comboBox) the piechart changes nicely. Even if i write a text inside the value fields there appears no error msg.

I first thought it might be a problem in the decimal separator on your system, but I changed that option from Point (.) to Comma(,) and in both cases worked fine.

Sorry.

P.D. I'm using Excel 2003 to test.

[Andy Pope]

I think this is a xl2007 issue/bug.

When your code cuts and paste some of the cells the contents of the chart is destroyed. At this point the chart contains Ref problems. Simply pointing the source data at the range again is not removing the problem.

[mhuston]

The formula in B7 is

=INDEX('Effekt og Energibehov'!$B$36:$N$36,0,$D$2)

This points to a different worksheet.

[ChrisNor]

The formula in B7 is

=INDEX('Effekt og Energibehov'!$B$36:$N$36,0,$D$2)

This points to a different worksheet.

Yes this is a one of the 3 hidden sheets in the workbook.. I don't see anything wrong with the formula though

[ChrisNor]

That simple...


Great thanks...

[Andy Pope]

I need to post an amendment to this as I stated something that was incorrect.

The CUT+PASTE destruction of the chart links is NOT a xl2007 bug.
This behaviour exists in previous versions, tested on xl2000.