Posts by GrahamB

  • Text To Columns Macro With Blank Cells

    Re: Text To Columns Results In Error


    G'day mcurry,


    Welcome to Ozgrid...


    There are two ways I can thing of doing this


    1. Error handling such as


    Code
    on error goto sydney 
' your code here


sydney:
'carry on with your code


    or 2 use an identifier such as

    Code
    if activecell <> "" or activecell <> 0 then
'your code here
else
end if
'continue with your code here


    HTH


    GB

  • Use Cell Name To Create Range..when The Vendor Changes

    Re: Use Cell Name To Create Range..when The Vendor Changes


    Hi Charlie,


    I have read your questiona few times and I am struggling to understand it - am I correct in your example Charlie should be b3:c5?? (not b3:c4)


    If you example is right can you please give a bit more detail to your question.


    Cheers,


    GB

  • Combine 2 Loops

    Re: Combine 2 Loops


    G'day komburajan,


    I have to agree with shg - tooooo much code.


    However, I did notice you have used 'j' to define 2 loops within the same macro - without going thru' the code line by line, you might try just redefining one of the 'j's to something else.


    I don't know if it have an impact, but when coding I always try and keep dim as completely separate entities so there is no possibility of confusion ...


    HTH


    GB

  • Enter Shift In Schedule For Printing

    Re: Enter Data In Table For Printing, Copy Data Into List Form


    G'day Cameron,


    Welcome to Ozgrid...


    Using macros can save time and energy but it can also be more complex that you might want, especially if you are not familiar with them.


    From the example you have given (and I am assuming the number of team members is a few more than what your are showing, and that your would be scheduling for a week at a time), may I suggest creating a template for the schedule (like your example 1) on sheet1, then on sheet2, create the format you want by using formulas. (see attached)


    This then can include other information such as Joe is not on and is seen as absent


    It also means it is a simple manual cut and paste or a simple macro to automate the process of cutting and pasting for use in your pivot table or sort it or print it or what ever you would like...


    HTH


    Gb

  • Transpose Vertical Data Horizontally

    Re: Transpose Vertical Data Horizontally


    hi socktrot1984 (Ben)


    These are the types of things you would think that Microsoft would get right - the code is reasonable simpe



    make sure you place the cursor on the first entry


    HTH


    GB

  • Remove Common Records Across 2 Workbooks

    Re: Remove Common Records Across 2 Workbooks


    Hi bodill,


    I can think of 3 ways to do this


    1. a macro that will - loop through your daily list and put a mark against all blacklisted entries - this is done by making the blacklist a named range in the blacklist sheet and being referred to in the daily sheet by the macro.


    The sort the list by the mark and delete them. Fairly complicated and requires the macro to be in the daily sheet


    2. insert a worksheet into your blacklist sheet and call it "daily" and andd a formula column that uses vlookup to determine if the entry exists in the blacklist, if it does mark as so - simple, quick, can sort it, no macros really required, cut a paste the list into an other sheet


    3. the 3rd way was ingenious but I forgotten what it is - probably the same as 2 but using macros


    I would suggest option 2 is the easiest and quickest


    HTH,


    GB

  • AutoFill Column With Relative Variables

    Re: AutoFill Column With Relative Variables


    hi Sam,


    This is an issue I face fairly regularly and the way I have overcome it is to right a simple looping code that goes through every worksheet, identified that it is a relavent sheet, and then copies, creates or adjusts whatever.


    it goes something like this



    Using this method, you can add, edit etc on both a mainsheet or all of the individual sheets.


    HTH,


    GB

  • Pause Macro - Select From A Drop Down - Restart Macro

    Re: Pause Macro - Select From A Drop Down - Restart Macro


    hi honoliipali,


    I have never attempted what you are asking, however, two thoughts spring to mind.


    1. Use two macros, the first initiates the start, the second as a cell change macro. ie by using

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    or 2 create a userform and use one of the selection controls.


    HTH GB

  • Sum By Condition & Other Tests

    Re: If And Sum Formula


    Probably the best way to do this is by using a sumif formula such as


    =E39-SUMIF(F10:F27,">1",E10:E27)-F39 (this is for cell k27)


    What it does is


    takes the total of 4153 (e39) less the sum of all numbers in column "e" only if there is nothing in column "f" less completed tasks sum at f39


    Hope this helps


    GB

  • Sum By Condition & Other Tests

    Re: If And Sum Formula


    hi kumara_faith,


    looking at your original formula - it must be circular as cell K11 is trying to calculate a number from K11.


    The post by darkyam is an array formula - very powerful and can cause greif if you are not farmiliar with them.


    The sum formula you might try is


    =IF(F11=0,SUM($E$39, E11, K11, -$F$39),SUM($E$39, -I11, K11)) and should be entered in another cell other then K11.


    Hope this helps


    GB

  • Active Cell As Reference

    Re: Active Cell As Reference


    Hi whisperinghill,


    The function you maybe looking for is


    CELL



    Returns information about the formatting, location, or contents of the upper-left cell in a reference.


    Syntax


    CELL(info_type,reference)


    Info_type is a text value that specifies what type of cell information you want. The following list shows the possible values of info_type and the corresponding results.


    Reference is the cell that you want information about. If omitted, information specified in info_type is returned for the last cell that was changed. The following list describes the text values CELL returns when info_type is "format", and reference is a cell formatted with a built-in number format.




    I copied the above from Excel help - so you can get from that info type settings.


    My preference for your problem would be to do it with formulas as it speeds everything up and it means not having to do a macro action.


    Hope this helps,


    GB

  • Active Cell As Reference

    Re: Active Cell As Reference


    I sense a challenge…


    How is the data arranged, is it like





    Col a Col b Col c
    Row 1 Child1 Mum1 Dad1
    Row 2 Child2 Mum1 Dad2
    Row 3 Child3 Mum2 Dad1
    … … … …
    Row 31432 Child 85 Mum16 Dad12


    If it is this way, then the dynamic range is (put directly into the “insert/name/define”) and call it something like "totaldata"


    =OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),3)


    This sets up the dynamic range to count all entries as they are added/or deleted. If you eliminate or leave blank lines you will have to adjust the formula for that.


    When you wish to select a child, you could use a drop down box using the dynamic range again but this time it would be and call it something like "kids"


    =OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)


    on the selection cell (where you select the kid for review), add your drop down box "data/validation/list" and put "=kids"


    then to get the names of mum, dad, grandma, grand pa, offspring 1,2 and 3 etc you could use vlookups using "totaldata" as the source array and base the selection on the kid, then mum, then dad etc.


    HTH


    G

  • Hide & Show CommandBars/Ribbons in 2007

    Re: Hide &amp; Show CommandBars/Ribbons in 2007


    Roy,


    I agree with your frustration, however, given that most endusers like to play with programs to see what makes them tick (and I do this myself) means the programs can be corrupted if certain things are not 'protected'.


    With Excel 2007, I will use this code (and for those that are interested, please note there a two routines, the 2nd is to return the displays to the original format)



    This is very simple and does not secure the ribbon, all that is required is to double click on the title bar and it all reappears.


    It is a start to my learning the new ways of Excel 2007, hopefully it wont hurt the brain too much...


    Cheers,


    G