Posts by Anonymous


Re: code tag check
another test

Re: code tag check
test post

Re: Add Total Columns Through VBA
Try the following code modification to one of the Subs. I'm unsure that I understood what the Present Total should count, so if I have that wrong please explain what it should count.
Code
Display MorePrivate Sub SendToWS(dic As Object) Dim e, i As Long, w For Each e In dic If Not IsSheetExists(CStr(e)) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = CStr(e) End If With Sheets(CStr(e)) .Cells(1).CurrentRegion.Clear .Cells(1, 2).Resize(, dic(e).Count).Value = dic(e).keys .Cells(2, 1).Resize(dic(e).items()(1).Count).Value = _ Application.Transpose(dic(e).items()(0).keys()) For i = 0 To dic(e).Count  1 If UBound(dic(e).items()(i).items) > 1 Then .Cells(2, 2 + i).Resize(dic(e).items()(i).Count).Value = _ Application.Transpose(dic(e).items()(i).items) End If Next 'New Code Start .Cells(1, 2 + i) = "Total" Range(.Cells(2, 2 + i), .Cells(2, 2 + i).Resize(dic(e).items()(1).Count)) _ .FormulaR1C1 = "=sum(RC2:RC" & 1 + i & ")" .Cells(1, 3 + i) = "Present Total" Range(.Cells(2, 3 + i), .Cells(2, 3 + i).Resize(dic(e).items()(1).Count)) _ .FormulaR1C1 = "=Count(RC2:RC" & 1 + i & ")" 'New Code End .Cells(1).CurrentRegion.Columns.AutoFit End With Next End Sub


Re: Calculate Current YTD variance from Last YTD figures
G15 is easy. Just use =SUM(G3:G14)
For H15 you will need =IF(F15="","",G15/SUMPRODUCT(E3:E14,SIGN(F3:F14)))
where SUMPRODUCT(E3:E14,SIGN(F3:F14)) is one way to get the appropriate sum from the E column.
You could instead use SUMIF(F3:F14,">0",E3:E14) for that total. 
Re: vba create pivot table
If you want to see where your original code went astray, then turn error checking back on and see what error messages occur.
Code
Display MoreSub MakeAPivotTable() Dim pt As PivotTable Dim cacheOfpt As PivotCache Dim pf As PivotField Dim pi As PivotItem On Error Resume Next Sheets("PIVOT").Select ActiveSheet.PivotTables("MyPT").tableclear2.Clear 'Restore error checking On Error GoTo 0 Sheets("DATA").Select Set cacheOfpt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("Data")) Sheets("PIVOT").Select Set pt = ActiveSheet.PivotTables.Add(cacheOfpt, Range("A1"), "MyPT") With pt .PivotFields("2 Day Checklist Submitted Date") = xlDataField .PivotFields("Team Indicator") = xlRowField .PivotFields("Account_Number") = xlColumnField .PivotFields("Data_As_of_Date") = xlColumnField .RowAxisLayout (xlTabularRow) End With End Sub

Re: Horizontal Tolerance Line
This is probably doable, but please post a sample workbook with the tolerance details and chart, to which you want the line added.


Re: Macro for Printing Excluding Formulas
Try the following to hide the lines. If it works on your data, then you can add back the other parts.
Code
Display MoreSub tryThis() Dim r As Range Application.ScreenUpdating = False With Range("A:A") .EntireRow.Hidden = False .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True For Each r In .SpecialCells(xlCellTypeFormulas, 2).Cells If Len(r) = 0 Then r.EntireRow.Hidden = True Next r End With Application.ScreenUpdating = True End Sub


Re: Tricky Percent Growth Problem
A Final time. Read your objective again. You are looking for a growth profile that matches the profile of the prices. The prices in this example do not increase by the same amount each month. To match their profile use Column D.

Re: Tricky Percent Growth Problem
Here is an example that may help. Suppose a stock starts at 1 and doubles every month, so its monthly values are 1, 2, 4, 8.
Then the monthly growth each month is 100% and the cumulative growth numbers are 100%, 300% and 700%.
Note that the differences in the monthly values of the cumulative growth can exceed the largest monthly growth rate.
Does this help? 
Re: Tricky Percent Growth Problem
I'm sorry, but we both seem to be repeating ourselves. The difference in Cumulative growth between two months is not, in general, the same as the growth from one month to the next If you want that to be the case then you would need cumulative growth to just be the sum of the individual monthly growths, which is not the standard definition.
I'm afraid I can be of no more help on this subject

Re: Tricky Percent Growth Problem
The logic you have been using to calculate the cumulative growth is correct, The logic you are using to doubt its correctness is faulty.
Cumulative growth in cell C464 should use the formula
=(B464/$B$217)1
where cell B217 has the original value and B464 the current value. (The ^(1/1) term you are using now is superfluous.) 
Re: Tricky Percent Growth Problem
The formula as written goes in cell D464 of Post Number 6.
Maybe it's time to pause and ask what results you really want to have. What is the purpose of this workbook? What question(s) is it trying to answer?

Re: Sum of the highest 3
Try =LARGE(A1:J1,1)+LARGE(A1:J1,2)+LARGE(A1:J1,3)

Re: Tricky Percent Growth Problem
To get the 19.3% decrease from the values in column C you need to use the formula
=(1+C464)/(1+C463)1 
Re: Tricky Percent Growth Problem
I think you are OK as the workbook stands (although the ^(1/1) term is unneeded and has no effect). Column C calculates the change from the previous month. Column D from the initial value in row 217. The differences in column D have no direct relationship to the values in column C because their base (the denominator) is different.

Re: CountIFS greater and less than statements
When you say COUNTIFS($D:$D,U$1,$K:$K,"<>12345") doesn't work, do you mean it gives the wrong answer? or that it gives an error?
Try the equivalent formula
=COUNTIF($D:$D,U$1)  COUNTIFS($D:$D,U$1,$K:$K,"12345")
Does this give the correct answer?