Posts by Jack in the UK

    Re: Select column if cell contains a certain word


    Something like might help to start

    Re: Call sum visible cells only function within sub


    I do not understand quite why or how You are using Your function as the returned long value is not used anywhere


    I suggest that You perfect the function first or deploy the result somewhere ie


    Code
    Sub Test_IT()
    
    
    MsgBox Sum_Visible(Range("a1:c10"))
    
    
    End Sub

    Re: Analyse Clipboard Before Pasting Into


    This code will only give You the text - something to play arround with at least, see if this helps? Test on a non work SpreadSheet first please


    Copy all these codes to a standard VBA module and attach buttons to the scripts


    jiuk

    Re: Select Sheet Code. Sheet Name Changes


    No ideas either, the codes easy enough to write, Jack assumes its a see it do it thing, easier for some to work this way which if the codes work is OK by Jack


    Depends on how teh codes used and why, up to the user really <shrug>


    jiuk

    Re: Replace Text On Non Active Worksheet


    This code can be run from any trigger / button on any sheet, and is fully defined. Try this on a copy of Your work just in case
    Set the named range of cause or teh code will bomb


    jiuk

    Re: Select Sheet Code. Sheet Name Changes


    Test this:

    Code
    Sub test()
    Dim nom_feuille_SHEET As Excel.Worksheet
    On Error GoTo 0
    Set nom_feuille_SHEET = Worksheets("nom_feuille")
    nom_feuille_SHEET.Select
    theEnd:
    Set nom_feuille_SHEET = Nothing
    On Error Resume Next
    Exit Sub
    End Sub

    Re: Maximum Length For A Macro


    A module is kind of like a file so its 256kb max in txt (*.bas) file format. That’s around 2000 lines last time we had this jack recalls this was kind of agreed on depending on the character layout per line etc.


    So even under fragmentation by procedure in a the same module this is a concatenated value, not sure re Excel 2007 thou, sorry about that.


    Also each module and procedure is loaded into memory at run time so it can slow of over bloated etc etc …. Long time since we trod this path, so hope this helps


    In short keep it short and tight, code cleaners can help and other tricks ie good codes


    jiuk[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Fencliff
    Sorry was typing Jacks own post - missed that, nice post. Thank You
    jiuk

    Re: Transfer Cells Values To Cell Comments


    Sorry for the delays, this version will be much better. I see You have an XLS for this but jiuk will post for the forum as others might be able to use or look at jiuks work


    jiuk

    Re: Trim Text Of A Cell With Max Num Characters


    Here You go a full UDF for you - of cause change left to right for the other version You asked for


    jiuk

    Code
    Function myLEFT_BY_6(TargetCell As range) As String
    ' written by: Jack in the UK
    ' Our WEB SIte - www.excel-it.com
    Dim myLeft As Integer
    Dim myVar
    myLeft = 6
    myVar = Left(TargetCell, 6)
    myLEFT_BY_6 = myVar
    End Function

    Re: Paste Cells As Comments


    Please test this code on a copy of Your work, just in case. Note only run once or Your end up with multi dup data entrys


    jiuk

    Re: Congratulations On 10,000 Posts, Andy Pope!!


    Andy Pope ????


    Andy is a very good friend and Jack speaks for Jack, Andys help is no less than brilliant and a more honest man Your never find, helpful to a fault and such an asset to OzGrid, Jack once again thanks Andy for OzGrid and and his friendship (putting up with jiuk also)


    Jack has learnt soooo much from Andy and a teriffic amount from reading his work and posts right here on OzGrid - a better man Your just never gonna find. Read his work? Your agree!


    Keep Roobarb-ing


    jiuk

    Re: Vba Formula -make It Run Automatically


    Something liek this might help or help You add code. The idea is to force excel to reculate ie run UDFs - unTested thou <yikes>


    jiuk

    Re: Yearfrac Calculation


    yearfrac - Fraction of year between dates
    Syntax
    YearFraction = yearfrac(StartDate, EndDate, Basis)


    Not sure about leapyear, it has always been a pain I reCall and something jiuk avoids. I have given the systax so You can check Your spot on just in case


    jiuk[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Also see ----
    Description


    YearFraction = yearfrac(StartDate, EndDate, Basis) returns a fraction based on the number of days between dates StartDate and EndDate using the given day-count basis. If EndDate is earlier than StartDate, Fraction is negative.
    Examples


    YearFraction = yearfrac('14 mar 01', '14 sep 01', 0)


    YearFraction =


    0.5041


    YearFraction = yearfrac('14 mar 01', '14 sep 01', 1)


    YearFraction =


    0.5000


    See Also


    days360, days365, daysact, daysdif, months, wrkdydif, year, yeardays


    NOTE __ Credit to the author of this information jiuk has nabbed it, be happy to edit the post on evidence of Your work, sorry buddy I cant remember its old stuff I have saved <shrug> As always full credit where due