# Posts by tinyjack

Re: Sum after 2 Vlookups doesnt work

Did you see my edit to my previous posts?

TJ

Re: Sum after 2 Vlookups doesnt work

So, what error do you get when you divide by 2?

TJ

Remember,

(a - b) / 2

is not equal to

a - b / 2

Re: Sum after 2 Vlookups doesnt work

Is the formula returning the expected answer before you try to divide by 2?

TJ

Re: Generate Rank Report

You can just use the RANK() function.

TJ

Re: VBA Unhide selected sheets with simliar names

You could use your same basic looping idea and use some code on the lines of:

[vba]
If Right(wChart.Name, 3) = "5YR" Then wChart.Visible = True
[/vba]

HTH

TJ

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

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]

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

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

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

Re: Recording macros into existing modules

Why do you want to do this?

Do you rename your modules to something meaningful?

TJ

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

Re: Splash means file doesnt open?

I would imagine it is a path issue. You can use Thisworkbook.Path to get the path or hard code in the full path of the file.

TJ

Re: named range implementation

Do you understand why it now works and how it differs from your original?

TJ

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

Re: Pick lowest numbers from group of numbers

Taxi, looking fare. Customer already left. But, hey the drive was fun.

Re: Pick lowest numbers from group of numbers

I assume you mean something like this:

TJ