Posts by Brandtrock

    Re: Norie Hits 9000 Posts


    Congratulations Norie,


    Your contributions here are much appreciated. I don't get to the forums as much as I'd like either. My PC is fine, in fact if I had your machine I'd probably get more net time on the net. If my sons had to endure more than a second or two to get onto a site, they'd leave the PC alone and I could use it!!


    Keep up the great work.


    Regards,

    Re: Running A Macro Automatically At The Start Of A Show


    Events in PowerPoint aren't used as easily as in Excel or Word. I have attached an example of how you can get this to work. You will need to utilize a class module in an add-in. Take a look and see what you think. Post back with any questions.


    I'll be gone swimming with my sons for a while, but I'll check back later.


    Regards,

    Re: Pivot Table Calculated Item Removal


    Dave,


    That simply changes the zeros to blanks. I think Pete is looking for a way to get the lines with a 0 GP to be hidden.


    Pete,


    The Pivot Table Item will calculate itself using the base data. Therefore, you are getting the extra zero values because the PT is figuring the Sales - COS for each contract under each BM in each Area.


    A calculated Field on the other hand only computes on the aggregated values in the pivot table. Unfortunately, the data layout you have makes this unworkable as the Category field contains both the Sales and the COS labels.


    If you are agreeable to a VBA solution, you could hide rows with 0 in the GP column containing zero and have it fire when the PT is refreshed. Perhaps not a perfect solution, but it would remove the button from the process.


    Regards,

    Re: Functions And Statements In Vba


    I found this in the Visual Basic Help:



    List of Worksheet Functions Available to Visual Basic


    Acos


    Acosh


    And


    Asin


    Asinh


    Atan2


    Atanh


    AveDev


    Average


    BetaDist


    BetaInv


    BinomDist


    Ceiling


    ChiDist


    ChiInv


    ChiTest


    Choose


    Clean


    Combin


    Confidence


    Correl


    Cosh


    Count


    CountA


    CountBlank


    CountIf


    Covar


    CritBinom


    DAverage


    Days360


    Db


    DCount


    DCountA


    Ddb


    Degrees


    DevSq


    DGet


    DMax


    DMin


    Dollar


    DProduct


    DStDev


    DStDevP


    DSum


    DVar


    DVarP


    Even


    ExponDist


    Fact


    FDist


    Find


    FindB


    FInv


    Fisher


    FisherInv


    Fixed


    Floor


    Forecast


    Frequency


    FTest


    Fv


    GammaDist


    GammaInv


    GammaLn


    GeoMean


    Growth


    HarMean


    HLookup


    HypGeomDist


    Index


    Intercept


    Ipmt


    Irr


    IsErr


    IsError


    IsLogical


    IsNA


    IsNonText


    IsNumber


    Ispmt


    IsText


    Kurt


    Large


    LinEst


    Ln


    Log


    Log10


    LogEst


    LogInv


    LogNormDist


    Lookup


    Match


    Max


    MDeterm


    Median


    Min


    MInverse


    MIrr


    MMult


    Mode


    NegBinomDist


    NormDist


    NormInv


    NormSDist


    NormSInv


    NPer


    Npv


    Odd


    Or


    Pearson


    Percentile


    PercentRank


    Permut


    Phonetic


    Pi


    Pmt


    Poisson


    Power


    Ppmt


    Prob


    Product


    Proper


    Pv


    Quartile


    Radians


    Rank


    Rate


    Replace


    ReplaceB


    Rept


    Roman


    Round


    RoundDown


    RoundUp


    RSq


    RTD


    Search


    SearchB


    Sinh


    Skew


    Sln


    Slope


    Small


    Standardize


    StDev


    StDevP


    StEyx


    Substitute


    Subtotal


    Sum


    SumIf


    SumProduct


    SumSq


    SumX2MY2


    SumX2PY2


    SumXMY2


    Syd


    Tanh


    TDist


    Text


    TInv


    Transpose


    Trend


    Trim


    TrimMean


    TTest


    USDollar


    Var


    VarP


    Vdb


    VLookup


    Weekday


    Weibull


    ZTest

    Re: Live Long And Prosper


    Thanks Will.


    Drink a pint or two for me, the boys are out of school for the summer now. First day of vacation and the complex pool is closed down due to failing a public health test!!! Guess I'll be going to the public pool with the lads (supposed to be 100+ Fahrenheit today).


    Regards,

    Will I live to see 80?


    I recently picked a new primary care doctor. After two visits and exhaustive lab tests, he said I was doing "fairly well" for my age.


    A little concerned about that comment, I couldn't resist asking him, "Do you think I'll live to be 80?"


    He asked, "Do you smoke tobacco, or drink beer or wine?"


    "Oh no," I replied. "I'm not doing drugs, either!"


    Then he asked, "Do you eat rib-eye steaks and barbecued ribs?"


    I said, "No, my former doctor said that all red meat is very unhealthy!"


    "Do you spend a lot of time in the sun, like playing golf, sailing, hiking, or bicycling?"


    "No, I don't," I said.


    He asked, "Do you gamble, drive fast cars, or have a lot of sex?"


    "No," I said.


    He looked at me and said, "Then, why do you even give a s**t?"


    Regards,

    This is an interesting [URL="http://books.google.com/books?id=FPMpZ2qMkKwC&dq=Secrets+of+Successful+Software+Requirements&pg=PA110&ots=tbjF4OgCWc&sig=-Pf1ev4KqCe1CPb_kJsIEWmcvaY&prev=http://www.google.com/search%3Fhl%3Den%26q%3DSecrets%2Bof%2BSuccessful%2BSoftware%2BRequirements&sa=X&oi=print&ct=result&cd=1#PPP1,M1"]book[/URL]. Not a how to program book, but a bit of a perspective builder as it looks at the development of software from a historical perspective and an eye toward the future.


    If you have some spare time, then consider picking it up.


    Regards,

    Re: Sorry for the Inconvenience Excel Needs to Shutdown. When Saving


    I had this problem when I was launching an instance of Excel in the background and not cleaning up properly (Setting my object = nothing).


    Everyone who wants to pile on can do so now.


    [SIZE="5"][COLOR="Blue"]Bad Brandtrock, now go to your corner for time out!![/COLOR][/SIZE]


    Anyway, I used the Task Manager to kill my rogue instances, and the problem went away.


    As with anything related to Excel, there are multiple reasons for the particular error message to be thrown about (because providing too many specific error messages with meaningful descriptions would cause MS too many headaches, especially when the end users can ask in any of a number of forums for help) so I don't know if my experience will relate at all to your specific problem. Just thought that I would share my experience just in case it helps.


    Regards,

    Re: Little Cells Inside The Main Cell


    When the Word file is open, press the Show/Hide Paragraph button. It looks like a fancy backward capital P. This shows where the enter key has been pressed to create lines in the "main" cells in the Word Document. Excel appears to be interpreting these as individual rows.


    Regards,

    Re: Vba Code In Crystal Reports?


    Give this guy a whirl, lots of information and good help there.


    Will is exactly right about database vs. reporting tool. If you simply want to suppress the display of duplicate records, Crystal reports has a couple of ways to do that within the report design.


    The fields that duplicates are in can be set up to not display duplicates, OR the selection criteria can be set up using SQL coding or Crystal basic syntax to only select the desired records.


    Crystal is hard software to provide "blind" help on. It is also hard to provide an example as the connection to the data isn't there, although that isn't always a complete roadblock.


    Check out Ken's site (link above) and see if he has anything related to what you need, he is by far the most experienced Crystal Reports expert I have come across on the web.


    Regards,

    I was working on a project the other night. Anytime a cell in column G was selected, I needed to check another column to see if there was data in it and, depending on that result, enter one of two results in column G.


    I won't divulge how long it took me beating my head against a brick wall trying to "fix" my selection change code. I was very distraught at how easily I had buggered up what was supposed to be a quick little code snippet.


    My 11 year old asked me why I was so upset. Figuring that talking it out might help, I explained to him exactly what was SUPPOSED to happen.


    He looked at my code and said, "You want it to work in which column?"


    I said, "G".


    I saw him using an age old technique that very often produces correct results. As he said each letter of the alphabet from A to G, he extended a new finger. At G, he had seven fingers held up. He smugly turned to me and said, "Why are you using 8 in your code when G is 7?"


    :crying:


    At least I got it fixed.


    Hope this brings those who read it some relief in the form of laughter, either with or at me, both are welcome.


    Regards,

    Re: Vba Assignment


    Neil,


    You seem sincere enough in your desire to learn with a helping hand on the sticky spots. I'm willing to take you at your word on that for now. I went to the link from your other post and read through what was assigned. I think I have a grasp of what the instructor is after from the students.


    Have you:


    • Followed the instructions to download the file?
    • Read through the sample code provided in the template?
    • Got an idea how you would solve this particular task on paper?


    I'm in the western US and have developed a nasty habit of staying up into the wee hours of the morning. My wife needs a ride to work in the middle of my night, so I just lurk here and at VBA Express until time to take her. All of that to say that I will download the file and look at the code sometime in the next 24 hours.


    I do have to squeeze in some time on this little project I have going called "The rest of my life", but will donate some time and effort to you provided you take the initiative and get to a sticking point.


    Regards,

    Re: Post


    Go to the Excel Help forum. Push the New Topic button. Enter an appropriate title, making sure to observe all of the rules you agreed to when you signed up. Describe your question in a clear, concise, and to the point way. Include the necessary details and the specific problem you are having.


    If necessary attach a file as an example. Use the manage attachments button below the main reply window.


    Regards,