Posts by foxy

    I've got two time series (growth rates: what was the forecast for any particular year and what was actual growth rate for that year). I need to compare them to flag those most different. Actually I want to check the accuracy of the forecast. What would be the best function to do it?

    I am sorry, but I've got a model I wrote a few years ago. Now I need to demonstrate it to someone. So I decided to try it before that and ran into a number of issues. As I remember I was writing it in Office XP, but I am checking it on Office 97 at the moment. So maybe there is incompatibilities between those to versions.


    To the point. The first error is with HorizontalAlignment. I've got the following code:

    Code
    Set lineStart = StartCell.Offset(Counter, 0)
            With lineStart
                [COLOR="Red"][B].HorizontalAlignment = xlLeft[/B][/COLOR]
                .Font.Bold = True
                .Font.Italic = True
            End With
                lineStart.Offset(0, 1) = txCoal2InvCost.Value
                lineStart.Offset(0, 2) = txCl2LT.Value
                lineStart.Offset(0, 4) = txCoal2MPC.Value


    and I've got the error:


    "Run-time error '1004'
    Unable to set the HorizontalAlignment property of the range class"


    I looked up HorizontalAlignment in the Help but do not see anything wrong with my code.


    Another problem with xlEvaluateToError
    the code


    Error:
    "Compile error:
    Variable not defined"


    I cannot find xlEvaluateToError in the Help. Maybe it is not compatible with Excel 97?


    I would appreciate any help or tips.

    Re: Scatter chart in 3D!


    Thanks Andy. Add-on works fine. The problem is that I have over 100 data points and to have labels for all of them is ver messy. But I canno figure out how to add labels manually to the data points. If I select a single data point I have in formula bar the following:

    Code
    =SERIES(Sheet2!$A$1:$A$133,Sheet2!$D$2:$D$133,Sheet2!$C$1:$C$133,1,Sheet2!$B$1:$B$133)


    So what should I do to add a label?

    Re: Name reference


    Thanks Will. There is no error in the original spreadsheet. Year, I now remember that I was struggling with dynamic range last time and did not find a solution. Please post if you you mange to find one.

    Re: Name reference


    Quote from WillR

    You have an error in either the reference cell (which has the name typed in it) or the name itself - one way or another, the two values differ - hence the #REF error - it happens when the reference specified does not exist.


    the fact is that they are identical. I have no idea, why it is happening :?

    Re: Name reference


    Quote from WillR

    I would check your data / typing, it works perfectly. I tested it.


    Your error is more likely to be what you have typed in G1 versus the name of the range.


    See attached


    It does not work for me in the real workbook, although works fine in the dummy. The name is right. I am still trying to figure out why it comes up with #REF.

    Re: Name reference


    Quote from WillR

    Yes, but it requires the use of INDIRECT


    =VLOOKUP(A1,INDIRECT(G1), 2,FALSE)


    Thanks for the reply. As I understand, INDIRECT returns reference, while I guess I need "value", lik in VBA where you do Range.Value. Anyway it did not work. I've got #REF thing...

    I've got several Names defined in a workbook. I need to use them in VLOOKUP. The problem is that if I use a reference to the cell where I've got a name of the range it does not work.


    For example, I have a name "red" which refers to range "$A$1:$B$78".


    Let's say, I've got a cell "G1" where I've got a word "red" written.


    So if I type something like =VLOOKUP(A1,G1, 2,FALSE) and I 've got #N/A
    If I do =VLOOKUP(A1,red, 2,FALSE), it works.


    Is there any way to make it work as a reference, rather than actual name typed in a formula?

    I tried to modify the code above to amend formulas in names. I need to change 2 to 1 in some cases. So that's what I came up with:



    I tried to Use Mid statement to replace 2 to 1. It does not work. I think I should use something else.


    I have attached the sample. As you see, since the number of non-empty cells in columns is different, I have to substitute 1 in second case, not two. But I ran a macro to insert formula and specified 2 everywhere. So now I need to change it ti 1 in many cases.

    Now it looks like this:



    and works. Thank you guys. One small question. It selects a worksheet where chart is embedded every time it prints. Is it possible to put it on the background so the sheet would not change?

    Well, with your help, guys, I managed to make all Captions to appear in message box.


    How can I use Caption to print appropriate charts? I tried


    Code
    Charts ("chb.Caption").PrintOut


    but it does not work. Since I do not have those sheets active, I cannot use


    Code
    ActiveSheet.ChartObjects("chb.Caption").PrintOut

    In the follwing code


    Code
    Dim chb As Control
    For Each chb In UserForm1.Controls
            If chb.Value = True Then


    The control does not have such properties as Value, TypeName, Caption I try dim it as an Object, it does not have any properties then. I want use Caption to assign a chart because Caption and Chart Name in most cases coincide and TypeName as you mentioned to sort out checkboxes.

    hi, I now want to print only selected charts, so I created a UserForm with checkboxes for all charts in the workbook. Thus I can choose the charts I want to print. I have 23 charts in the workbook. I want to write code where Excel prints those charts where CheckBox value is true.
    I wanted to write something like


    Code
    Dim chv as Object
    For Each chv in UserForm1.CheckBoxes
       If chv.Value = True Then
                      Chart.PrintOut
       End If
    Next chv


    But UserForm1.Checkboxes obviously does not work. And how can I bundle the checkboxes and appropriate charts?