Replies

Thanks Alan

I did not have that option enabled as there is a need to use the figures as precisely as possible. In this case, that meant that a value of 0.46 had to be considered other than the MS Excel calculated value of 0.460000000…08

My problem was answered by Legare (and Bat17 raised an issue with the same formula) although you make a good point of which others need to be aware.

Leigh

The original MS Excel workbook (not the sample file posted by me) was actually intended to return text as both the True and False arguments. It relies on values being met to return the ‘text’ .

The solution came when Legare advised that the binary conversion could be allowed for by using the ROUND function. This did solve my particular case, mainly because the results in C7:C10 were not simple values, but the calculations returned from the values in B16:C18.

Many thanks for your input, Leigh

Thanks for everyones’ replies.

I rushed to create the workbook and did put the closing bracket for the MAX function in the wrong place. (The PC I sent from did not have MS Excel to check before sending. What about Internet Explorer for reading Office files, I hear you ask… I forgot!)

I had heard about binary rounding but had not investigated it, but Legare’s response hit the nail right on the head, again.

I am grateful, Leigh

I would like to give you the MS one but my History gives a link that will not work today although the following is what is showing.

http://download.microsoft.com/download/exc…-us/recover.exe%5B/url%5D

But, for the good news, I found these other two sites that may assist along the same lines.

http://www.oshvn.net/en/software/microsoft…le_recovery.htm%5B/url%5D

http://www.erlandsendata.no/english/downloads/tools.htm%5B/url%5D

HTH, Leigh

I have tried the Recover.exe file posted on MS site and it seems to have done the trick. (Although many changes to VBA code have happened since the problem appeared.)

All it means I suppose, is that if the XL2000 code has the same opportunity to expose this (ancient) dialog box, it will; and like the version before it, be unable to do anything constructive and just freeze.

Leigh

If anyone has an interest in this problem, the solution is simply to use the following reference when building the formula.

ActiveCell.Formula = "=IF(OR('WSheet'!A10="",'Results Sheet'!N10="ERROR"),"", ...." 

This keeps the formula style that it was created in, and not FormulaR1C1 style.

Leigh

You might be right about XL file being corrupted because it came up with the message “Unable to quit Excel”.

Whilst I have experienced this before (once or twice) I assumed it was a mistake in my VBA code.

Another point, I am in the practice of working on an XL97 project, closing XL97 then opening the project in XL2000 because its compiler reduces the file size much more efficiently than XL97 does.

Could this be part of the problem? (There is no XL2000 specific code added, I only use the Debug | Compile VBAProject command in XL2000.)

Regards, Leigh

Hello Legare

Not to my knowledge… The PC does run concurrently both XL97 and XL2000 but that should not cause the problem. The PC has never had any version before XL97 installed so the dialog can only have surfaced through the MS code that runs XL.

Whilst I have seen it on an earlier PC that also ran XL97, it was upgraded from XL ver 5 to XL97.

Curious. I wonder if any Microsoft people know what is going on and why when the dialog appears, it locks up the system.

Leigh

Thanks Leanne

Several of my macros create tables (that are populated by a userform). In one case, a table is then VBA cut and pasted into the header (it contains document control info).

So I will have to run a thorough test to check that.

Thanks again, Leigh

Thank you Leanne and Sue for your replies.

Leanne, might I enquire as to what the ‘couple of problems with macros’ were, as one of my templates has both Wordbasic (from the Word 6 migration to W97) and VBA (W97) macros?

Thanks in advance, Leigh

Thanks Legare!

That is exactly what was needed…

Now I will edit just 2 or 3 of the 21 lines of code that can be cut and pasted and replace 25 (sometimes 50) lines that had to be manually edited for each and every line before.

I will now go back and convert the 30 odd templates with this easier code.

Thanks again.

Thanks for your reply Legare.

The workbook does need reference other workbooks (but not in the formula I posted). I am looking for a way to ‘write’ the code so that I don’t get the following example but something that adds the reference to additional worksheets (if they are added by the user). The Update Links dialog will appear if the formulae are placed directly in the worksheets and the user will not know how to resolve a formula that references an non-existent worksheet (but still needs the update to happen for those formulae referencing external workbooks.)

Clear as mud??

Range(“C134”).Select

ActiveCell.FormulaR1C1 = _
“=IF(Details!R9C2<2,"""",IF('Results Sheet (2)'!R41C6=""ERROR"","""",'Work Sheet (2)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<3,"""",IF('Results Sheet (3)'!R41C6=""ERROR"","""",'Work Sheet (3)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<4,"""",IF('Results Sheet (4)'!R41C6=""ERROR"","""",'Work Sheet (4)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<5,"""",IF('Results Sheet (5)'!R41C6=""ERROR"","""",'Work Sheet (5)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<6,"""",IF('Results Sheet (6)'!R41C6=""ERROR"","""",'Work Sheet (6)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<7,"""",IF('Results Sheet (7)'!R41C6=""ERROR"","""",'Work Sheet (7)'!R11C7))"
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"=IF(Details!R9C2<8,"""",IF('Results Sheet (8)'!R41C6=""ERROR"","""",'Work Sheet (8)'!R11C7))"

Only the reference to the worksheet name is changing and that requires a lot of manual editing.

Is there a better way?

Thanks, Leigh

Thanks for your response Legare, but this is not an available solution.

The finished certificates are stored on a shared network drive and another user may be required to print it and ‘discovers’ the pagination has gone astray but cannot modify the layout as it is on protected sheets (to provide a consistent layout).

Any other suggestions?

Hi Wassim

Thanks for your reply. I will try it out later today.

I might give the users a toolbar button to insert the image then get them to click and drag the object to its final destination to solve the problem of screen resolution. Thanks again.

Leigh

Hi Diana

Do they really need to be stored as long or integer values?

What about a simple string? Are you using the values in calculations that absolutely require numbers?

Leigh