Posts by tinyjack

    Re: VBA Unhide selected sheets with simliar names

    Quote from bvan

    is it possible use a selection where name is similar to 5yr trends, as opposed to selecting all?

    I think you might need to expand on the above.

    Five year trends
    5 year trends
    5 yr trends
    Trends 5yr

    are all similar.

    or do you mean

    5yr trends Company A
    5yr trends Company B
    5yr trends Company C

    As the approach for each will be completely different.


    Re: Write formula from VBA (With ActiveCell.FormulaR1C1)

    There are 2 main schools of thought on including quotes characters inside a string. You either put 2 in where you want 1 to appear

    Debug.Print "Here is a quote "" character"

    'or break the string and add in a Chr(34)

    Debug.Print "Here is a quote " & Chr(34) & " character"

    It all comes down to which you feel more comfortable with.




    Formula = _
    "=IF($B" & FormulaNum & "="""","""",VLOOKUP($B" & FormulaNum & ",INDIRECT($F$6),2,FALSE))"

    Re: Adjusting the minimum value of the axes

    The lines:

    Set StartCell = Cells(rStartIndex, 2)
    Set EndCell = Cells(rIndex, LastCol)

    'are the same as

    Set StartCell = ActiveSheet.Cells(rStartIndex, 2)
    Set EndCell = ActiveSheet.Cells(rIndex, LastCol)

    This will cause an error if the ActiveSheet is not the same sheet as refered to with SheetName.

    I would think you want to do:

    If optGatControl Then
    SheetName = "GatControllables"
    Else: SheetName = "GatPSS"
    End If

    With Worksheets(SheetName)

    'Get count of active columns in the selected range
    LastCol = .Cells(rIndex, 256).End(xlToLeft).Column
    Set StartCell = .Cells(rStartIndex, 2)
    Set EndCell = .Cells(rIndex, LastCol)
    Set CurrentRange = Range(.Cells(rStartIndex, 2), .Cells(rIndex, LastCol))

    End With

    If ActiveChart Is Nothing Then ActiveSheet.ChartObjects(1).Activate

    ActiveChart.SetSourceData Source := Range(StartCell, EndCell), PlotBy:=xlRows
    ChartUnit = ActiveChart.Axes(xlValue).MajorUnit



    Re: Get report on a month by month basis

    Because your base data is cumulative, you would have to create 12 columns where you could copy each month's cummulative P&L position to and then you could calculate the monthly P&L as the difference.


    Re: Write formula from VBA (With ActiveCell.FormulaR1C1)

    1) Since you are not using R1C1 notation I would just use .Formula instead of .FormulaR1C1

    2) You need to use the US style for the formula, which means using , separators rather than ;. (I would guess you are in a country that uses decimal commas rather than decimal points)


    Formula = _
    "=VLOOKUP($B" & FormulaNum & ",INDIRECT($F$6),2,FALSE)"

    ActiveCell.Formula = Formula



    Re: deleting shapes

    Select Case is definitely the way to go with this, but do you realise the logic error in your original code?

    Your 1st If was deleting all the objects except "histo", which would have allowed "Button1" to get deleted.

    Your 2nd If was deleting all the objects except "Button1", however "Button1" would have been deleted in your 1st If and you would have now deleted "histo" that you tried to preserve in your 1st If.


    Re: named range implementation

    You have broken the 'r' out of the string to make the name of the range, but have left it in the string with the RefersTo. You need need to build the RefersTo string in parts.