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

    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


    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>


    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


    Re: Select Sheet Code. Sheet Name Changes

    Test this:

    Sub test()
    Dim nom_feuille_SHEET As Excel.Worksheet
    On Error GoTo 0
    Set nom_feuille_SHEET = Worksheets("nom_feuille")
    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

    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


    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


    Function myLEFT_BY_6(TargetCell As range) As String
    ' written by: Jack in the UK
    ' Our WEB SIte -
    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


    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


    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>


    Re: Yearfrac Calculation

    yearfrac - Fraction of year between dates
    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 ----

    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.

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

    YearFraction =


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

    YearFraction =


    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