Posts by Todkerr

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Print To Pdf - Auto Save As


    Does the box caption say:


    "print to file"


    with a message saying:


    "output filne name:"


    if yes, then the box is related to P2F.


    no one likes sendkeys....but will


    Code
    Sub Macro3() 
    
    
        Application.ActivePrinter = "Adobe PDF on Ne05:" 
    SendKeys ("c:\mypath...")
    SendKeys ("~")
        ActiveWindow.Worksheets("Sheet1").PrintOut From:=1, To:=1, Copies:=1, ActivePrinter:="Adobe PDF on Ne05:", Collate:=True 
    End Sub


    Make a difference (will probably still see printing dialog - but no user input...?)

    Re: Sum of X Largest Values


    Jindon's formula will work great - but if you need to define top 50 or top 150 numbers, or if you wanted the number to change as a variable etc. ...


    =SUMPRODUCT((RANK(A1:A30,A1:A30,TRUE)>(COUNT(A1:A30)-15))*A1:A30)


    might be easier to manage. (where "-15" is changed to -n for the sum of the n largest numbers.)

    Re: Time Range In A Single Cell


    Simplest solution:


    1) Highlight the data you want to convert
    2) in excel: data>text to columns...
    3) choose 'delimited' (next>)
    4) choose 'other' and type in a hyphen ('-')
    Finish


    This should give you a start time and an end time in two different columns. Subtract the start time from the end time in a 3rd column (simple formula "=b1-a1") where b1 has the end time and a1 has the start time. Convert this formula to a time format like "13:30"



    You can sum times into hours totaling greater than 24 by using a time format like "37:50:55"
    further help on time math can be found at
    http://www.ozgrid.com/Excel/date-time-calculations.htm

    Re: Kill Zeros From Number


    a sub with a sense of humor?


    now for the curtain call:


    Code
    Sub lingual (with cheek)
    dim wit as long ' as in Tod's code is too long
    with wit 
    wit = wit/2 '  about half the time, anyway
    if 
     end if
    my_unnecessary_ifs.ignore
    end sub

    Re: Kill Zeros From Number


    there is probabaly a more efficient way to do this...but this will work, I think (highlight the cells you want to change....)


    Re: Skip Column In Select Case


    Try this:

    Re: Run Input Text Box & Save To Sheet


    Jim,


    See if you can make a version of this work for you:


    Code
    Sub beyond_reason()
    Dim Reason As Variant
    
    
    Reason = InputBox("Why did you revise this?")
    Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Reason
    Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Now()
    End Sub


    This pastes the reason and time of change into column A and B on "revision" sheet.

    Re: Changing Multiple Worsheets With One Action


    Protecting could be done with:



    Unprotecting could be done with:



    Sorry - gotta go - perhaps someone else could help with the spell check.

    Re: Sum Of Postal Code By Region(canada)


    Of interest to me since CanadaPost appears to have LOST MY EBAY PURCHASE!!!!! LOL


    if you want sums then maybe a different approach


    edited to remove unnecessary pareth:


    =SUMPRODUCT(ISNUMBER(FIND(A12,$C$3:$C$8))*$D$3:$D$8)


    in B12 and copy down

    Re: Optimizing My Code


    Without knowing what is on this sheet...


    l

    Code
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
        Rows(lastrow & ":" & Rows.Count).Delete Shift:=xlUp


    Looks like it could be time consuming....


    can you accomplish the same thing faster with


    l

    Code
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
        Rows(lastrow & ":" & Rows.Count).clear

    Re: Hiding Columns By Date


    But correct me if I am wrong - and I often am!!!!


    Code
    Range("A1", .Cells(1, Columns.Count).End(xlToLeft)).Address)


    will return a1 to the last visible cell, not the last used cell.


    If A1-H1 is January though July - and July is hidden becuase it is now June....and saved that way...


    Next month in July - your sub won't unhide July because H1 will not be part of the for loop.


    Maybe I am missing something. I guess I'm assuming that the current month will be the last visible column with data in Row 1. If this is the case....then you need to force eval of the hidden rows - if this is not the case then your sub will work.

    Re: Hiding Columns By Date


    and....not to be a pain back Reafidy -


    Your sub omits the line:


    Code
    Cells.EntireColumn.Hidden = False



    I believe that if this is ommitted, previously hidden columns that become current/past as time progresses will not be made visible by your sub.

    Re: Hiding Columns By Date


    Assuming all column headers are in row 1.


    can you make something like this work:


    Code
    Sub auto_open()
    Cells.EntireColumn.Hidden = False ' unhide all columns when you start
    
    
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Select
    For Each x In Selection.Cells
        If x.Value > Now Then x.EntireColumn.Hidden = True
        Next x
    End Sub


    You might have to play a touch with the date formatting to get the current month to show properly.