Posts by mangier

Re: Vba Code To Open A New Workbook

Works like a charm. Thanks Simon.

I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;

workbooks.add

will open a new workbook now I just need to name the new workbook based on text in my existing workbook.

Re: Refer To Dynamic Range Indirectly

Thanks for looking at this. I must have mistyped something because this totally works. Sorry for posting this question. Moderaters please feel free to delete this thread.

Is there an equivalent to refering to a range of cells indirectly the way you can refer to a single cell indirectly using the INDIRECT() formula or something like it? If I type "B1" into cell A1 and type "=INDIRECT(A1)" into cell C1 then the result will be what ever is in cell B1.

How do I pass a range of cells into a formula indirectly (i.e. the contents in cell A1 is "A2:A100" and I want to pass this into another formula). This doesn't work but maybe it will illustrate what I am trying to do;

=SUM(INDIRECT(A1))

where A1 contains the text string A2:A100

Re: Extract Top X Maximum Values

Thanks again for your recommendations. I like the use of the MIN feature in your pivot table and it makes sense to uncheck the items with zero balances but this would be tedious if you had a few hundred items to scan through and uncheck all the zero balances. I was still hoping someone might have a more automated approach to solving this with formulas. I can always write a macro to do this (I am familar with manipulating and automating pivot table w/ VBA code) but it is nice and usually educational to find alternative solutions that can be gained from just using formulas.

Re: Extract Top X Maximum Values

This is a great solution for extracting the non zero smallest values but I don't know how to extract the adjacent information to the smallest values. In otherwords, if you have a table of names and new salaries (as I do) and you want to extract the lowest new salaries and their names then you need a vlookup or matching and indexing formula to get the names with the lowest values. I also have zero value salaries within this data from terminated employees whose new salaries are now zero so I need to exclude those names and values when extracting the data.

This array formula works great for extracting the names associated with the five highest values;

=INDEX($A$2:$A$8, MATCH(LARGE($B$2:$B$8 - ROW($B$2:$B$8) / 10^5, ROW() - ROW(A$11) + 1), $B$2:$B$8 - ROW($B$2:$B$8) / 10^5, 0) )

where column A has the names and column B has the salaries and Cell A11 is where you want to start this formula. Now how do I combine your straight forward formula;

=SMALL($B$2:$B$8,COUNTIF($B$2:$B$8,0)+ROW())

with the above formula to find the names associated with the lowest new salaries in column A? A vanilla vlookup formula will have problems indentifying several employees with the same salary (it will likely show the 1st occurance of the name and not subsequent names with the same salaries) but there may be a more crafty way to structure a vlookup with a dynamic range argument to pass by the 1st occurance after it has found it and move on to the next one.

Re: Extract Top X Maximum Values

Thanks again for your help Dave. Pivot Tables and custom views are all good options but I am trying to stick to the array formulas because of the versatility.

Can anyone help with a formula for extracting the 5 smallest values that are non zero?

=INDEX($D$2:$D$8, MATCH(SMALL($E$2:$E$8 - ROW($E$2:$E$8) / 10^5, ROW() - ROW(D$11) + 1), $E$2:$E$8 - ROW($E$2:$E$8) / 10^5, 0) )

Unfortunately, this one above will extract zero values from a data range as the smallest 5 values.

Re: Extract Top X Maximum Values

Dave,

This is pretty neat and I am learning more about using the advanced filter but I am still having trouble so I have attached the sample again to show you what my results of the Advanced Filter were. I have included the exact parameters I am using in the Advanced Filter dialog box. For some reason my results for the bottom 5 show the zero value and not the "1" value and they do not appear in ascending order. And the top 5 does not appear in descending order.

Also this approach, although very useful, is not as automated as the array formula approach which has the advantage of updating the results dynamically without the user having to rerun the advanced filter if the the data is modified.

Mark

Re: Extract Top X Maximum Values

Dave,

I've attached a representative workbook sample for the top 5 and bottom five with non zero values.

Thanks,
Mark

Re: Extract Top X Maximum Values

Dave,

The other formulas above work fine but I wanted to try your advanced filter approach since I thought that was going to be the best solution when I first tried to solve this problem but I keep getting "reference is not valid" which means I am obviously using this approach wrong.

I am also trying to use this knowlege to extract the 5 smallest non zero values from my data range as well. Any further suggestions would be greatly appreciated. I can attach another sample workbook if that would be easier to show you where I am having problems with your advanced filter solution.

Thanks,
Mark

Re: Extract Top Five Maximum Values From A Range

I hate when the solution is so eloquent that I would have had no chance to solve it on my own. Its like getting check mated in the first 10 moves. Thanks VBA Noob.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]VBA Noob,

I found a minor glitch that I can't sort out. If I insert a row above my data range and modify your formula in the following way, it will not show the two largest values;

=INDEX($A$2:$A$8,MATCH(LARGE($B$2:$B$8-ROW($B$2:$B$8)/10^5,ROW(A2)),$B$2:$B$8-ROW($B$2:$B$8)/10^5,0))

I have attached a sample workbook to show how your original formula works and how my modified formula fails.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]OK I found my error. The formula needs to be altered in the following way;

=INDEX($A$2:$A$8,MATCH(LARGE($B$2:$B$8-ROW($B$2:$B$8)/10^5,ROW(A1)),$B$2:$B$8-ROW($B$2:$B$8)/10^5,0))

I have changed the row reference from Row(A2) to Row(A1) in the Match formula. Hope this helps anyone else who looks at this thread.

How can I extract a list of the top five maximum values from a range of cells. This is similar to using the the "top ten list" in the Auto Filter but I don't want to filter my data in place. Instead I want to extract a list sorted in descending order along with desciption data located in adjacent columns for each maximum value.

In other words take the following list with descriptions in column A and values in column B

Item 1 = 10
Item 2 = 10
Item 3 = 2
Item 4 = 20
Item 5 = 4
Item 6 = 100
Item 7 = 5

and extract the top five values and their desciptions to another location in descending order

Item 6 = 100
Item 4 = 20
Item 1 = 10
Item 2 = 10
Item 7 = 5

I am wondering whether this can be solved with a combination of matching functions and indexing functions or whether I will need VBA code to solve this.

How can I change the text color within a cell when I am using a concatenation formula? I have the following formula;

="Total Bonus Earned ("&TEXT(A2/VLOOKUP($A$1,PR_DB,2,FALSE),"#0.0%")&" of 2006 Earnings):"

In the text function portion I would like to format the result in red. An example of the result of my formula I am seeking is as follows;

Total Bonus Earned [COLOR="Red"](10% of 2006 Earnings)[/COLOR]:

Do I need to create a custom format and if so how do I call it up within the text function?

Re: Import Access Query

Ok, now that I am in the right forum (Thanks Moderators) I did some searching and found this link about importing from Access with VBA.

http://www.ozgrid.com/forum/showthread.php?t=19625

I am struggling a bit because I don't understand the "tip" fully.

Quote

What we need:
* Microsoft Windows 2000 or above
* Microsoft Excel 2000 or above
* MDAC 2.5 or above
* A Jet database (mdb)

Step 1
Add a reference to the Microsoft ActiveX Data Object x.x library.

Step 2
Insert following procedure in a standardmodule:

Display More

In Step 1, I don't understand what reference I need to add to the ActiveX Data Object Library

In Step 2, do I enter this code into a standard module in Excel via the VBEditor or do I put this code into Access (I am very inexperienced with Access obviously)

And finally, what is MDAC? Is that ActiveX?

I am trying to automate the importing of several Access queries into Excel. I am somewhat proficient at writing VBA code for applications within Excel but have never tried to write code to import data to Excel.

Access does a nice job of exporting its queries to Excel (Tools|Office Links|Analyze it with Microsoft Office Excel) but I don't know how to change or direct its default path. It seems to create an Excel file with a "dbo." prefix followed by the query name and put it into the "My Documents" folder. I would be very happy just to learn how to change that default setting to direct the query results to a specific page with in a desired Excel workbook.

Ultimately, I would like to learn how to run an Access query and export it to Excel entirely through VBA code which is initiated within Excel. I would appreciate any suggestions on how to go about learning this process.

Re: Count Pivot Fields In Pivot Table

Thanks Will.

I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()


    Dim ghost As PivotItem
    Dim pt As PivotTable
    
    Set pt = ActiveSheet.PivotTables(1)


    pt.ManualUpdate = True


    For Count = 1 To 10
        On Error Resume Next
        For Each ghost In pt.PivotFields(Count).PivotItems
            ghost.Delete
        Next ghost
    Next Count


    pt.ManualUpdate = False
    
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient.
In otherwords;

For Count = 1 To Total_Number_of_PivotFields
        On Error Resume Next
        For Each ghost In pt.PivotFields(Count).PivotItems
            ghost.Delete
        Next ghost
    Next Count

Also, I don't know whether I need to turn on the ManualUpdate to speed up the code but I thought I would try it.

Re: conditional format to identify formula cells

Krishnakumar,

I don't understand your solution. I have never written a UDF so that is part of my problem. Is "HasFomula" the name of this UDF?

I tried Bob's code

Function Is(rng As Range) As Boolean 
    If rng.Count > 1 Then 
        IsFormula = CVErr(xlErrValue) 
    Else 
        IsFormula = rng.HasFormula 
    End If 
End Function

but I wasn't successful in setting up the UDF. Is his UDF named IsFormula with rng as its argument?

Re: Get Pivot Table Name

Thank you Will. I have written some generic VBA code that allows one to show all pivot items for a given pivot field in a given pivot table. With your last addition in line 5 below I think this code seems to be pretty bullet proof now. I am fairly new to writing VBA code so if you have a chance to review this subroutine I would appreciate your feedback.

Sub ShowAllPivotItems()
     
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim ptName As String, pfName As String, defaultName As String
    
    ptName = ActiveSheet.PivotTables(1).Name
    
    Set pt = ActiveSheet.PivotTables(ptName)
    
TryAgain:
    defaultName = "ABC"
    pfName = Application.InputBox( _
        Prompt:="Please Enter a valid PivotField Name in the Text Box Below", _
        Title:="Valid PivotField Name", _
        Default:=defaultName, Type:=2)
    On Error Resume Next
    Set pf = pt.PivotFields(pfName)
    
    If pf Is Nothing Then
        MsgBox ("You must Enter a Valid PivotField Name.  Please try again.")
        GoTo TryAgain
    End If
     
    pt.PivotFields(pfName).AutoSort xlManual, pfName
    pt.ManualUpdate = True
     
    For Each pi In pt.PivotFields(pfName).PivotItems
        pi.Visible = True
    Next pi
     
    pt.ManualUpdate = False
    pt.PivotFields(pfName).AutoSort xlAscending, pfName
     
End Sub

How do you get the name of a pivot table in VBA? I recorded a macro to see what happens when you change the Pivot Table name in the Pivot Table Wizard and I got the following;

ActiveSheet.PivotTableWizard TableName:="PivotTable2"

So I thought I would try this code;

PivotTableName = ActiveSheet.PivotTableWizard.TableName

but it doesn't work. Please help so I can positively select a pivot table in my VBA code without having to always check its name manually and then enter it into the code.