Posts by shades

    Re: Automatically Detecting Next Row But Not If Previous Row Was Deleted


    Quote from composedthought


    The problem with this, is that when I have to go back and delete something on the master page (in my case right now, for testing, but in the case of future, accidently entering the wrong information), it will keep going in order even if the rows before it are blank. The best example would be I have data in rows 1, 2, 3, 4, but I may delete 4, next time I enter data in the other page and have it update, it goes to 5 instead of 4 where I previously deleted the row, leaving a blank row.


    The only solution I could think of, is leaving a text box and button on the master page, and when I mess up, entering the number of the row I want it to start at, hitting the button, and that number being stored as a public variable. My biggest problem is I don't know how to properly use vba syntax to say, "use the number stored in this variable" instead of an actual number.


    -Matt


    Howdy.


    If you delete the row 4, then you won't have a blank row 4. Or have I missed something?

    Re: Pivot Table Columns In Month Order


    My suggestion is to change the macro to change the order of the columns first in the pivot table before making the pivot table. If you have the book VBA and Macros for MS Excel by Bill Jelen and Tracy Syrstad, chapter 12 has excellent advice about code for Pivots and several examples.

    Re: Pivot Table Columns In Month Order


    Howdy. Are you creating the Pivot Table new every month? If not, you can move the month to the correct position. Also, do you have the full date (i.e. 2/1/2007 for Feb - US format), or just the name (Feb)?

    Re: Data For Chart Changes When Adding A Row In Source Data


    Howdy. So, you have only one series, but could have 2000 data points or more as you add data?


    Dynamic named ranges are best in this environment. Without seeing your setup it is hard for this old codger to visualize what you have. Can you provide a miniature sample of how you have it set up? If sensitive data, then use dummy data.

    Re: Special Day Of Celebration


    My wife and I are blessed with our life together and felt happy about the number of years. But in worship this morning, we prayed for a couple who are celebrating 72 years of marriage tomorrow!!!

    Re: Option Explicit


    Just a note regarding variables in coding, it is best that you don't use normal Excel/VBA words as variables. That is, it is better to use the first line, not the second


    Code
    ' better way
    Dim Cel as Range
    
    
    ' not as good
    Dim Cell as Range

    Re: Decimal Place Rounding Error


    Howdy. There is a difference between the stored value and the displayed value. On top of that, a number has limits (utlimately 15 characters), whereas a number that is really text can have as many characters as normal text cells (1,024... but I have seen where the number of characters in a cell approaches 32,000).

    On Tuesday, my wife and I celebrate our 36th wedding anniversary. It was cold that night: -40 degrees F. Our honeymoon lasted one night. ;) I had to go back to college on Monday morning - I had a month of classes until I began student teaching and then graduating in May of that year.


    We have survived 27 moves, my four career changes, two kids, and 4 grandkids. Many ups and downs... but God has been gracious to us and faithful, and we give him thanks.

    Re: Count With Sumproduct And 3 Criteria Range


    Howdy. I think you don't need the AND, maybe something like this?


    =SUMPRODUCT(--(Data!$A$2:$A$209=$A11)*--(Data!$C$2:$C$209>=$B$1)*--(Data!$C$2:$C$209<=$B$8)*--(Data!$J$2:$J$209>=$B$10),Data!$J$2:$J$209<=$C$10)))

    Re: Option Explicit


    Howdy. Option Explicit forces the one using VBA to insure that all variables are properly defined. It is best to have it set for everything you do in VBA. Sometimes there is a problem with shifting what a variable is, and it doesn't match what you are trying to do.


    Can you post the code you are using?

    Re: Bar Chart With Marker


    Okay, that triggered something that Charley Kyd had done a while back. How to Create Bullet Graphs To Replace Gauges in Excel. I think this might very well get you what you want.


    His site Excel User is a storehouse of superb insight and tricks that also should be bookmarked. I had participated in the tele conference seminar a year ago - best investment our company has ever made for XL reporting (and we are a Fortune 40 company). In fact, I have been tasked with training many of our departments in Marketing (~2,000).