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 Private 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 Sub 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: Displaying total # of names between dates into a different column based on a mont
In column J try the following formula.
=COUNTIFS($M$27:$M$40,"<="&A4,$N$27:$N$40,">"&A4) 
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 Sub 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?