Posts by Anonymous

testing url

http://www.ozgrid.com/Services…cial-advisor-business.htm

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.

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: If Statement Question

Try

If PDBenTyp = "BA" Or PDBenTyp = "EA" Then

Re: Calculate Current Y-T-D variance from Last Y-T-D 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.

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.

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: Macro to unlock worksheet enter data and the protect it again

The problem line is missing a comma after myPassword. It should be

ws.Protect Password:=myPassword, UserInterfaceOnly:=True

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?