Posts by tinyjack

  • VBA Unhide selected sheets with simliar names

    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.


    TJ

  • Write formula from VBA (With ActiveCell.FormulaR1C1)

    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


    [vba]
    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"
    [/vba]


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


    HTH


    TJ


    ps


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

  • Adjusting the minimum value of the axes

    Re: Adjusting the minimum value of the axes


    The lines:


    [vba]
    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)
    [/vba]


    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:


    [vba]
    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
    [/vba]


    HTH


    TJ

  • Get report on a month by month basis

    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.


    TJ

  • Write formula from VBA (With ActiveCell.FormulaR1C1)

    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)


    Try:


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

    ActiveCell.Formula = Formula
    [/vba]


    HTH


    TJ

  • deleting shapes

    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.


    TJ

  • named range implementation

    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.


    TJ