Posts by turtle44

    Re: Find Text In A String Of Text And Return Whole Found Text


    Quote from kamoore

    that formula will look in a list of "long text" in column b and return the specific long text that includes the "short text"?


    I know. That seems to be what you asked, although I'll admit your question wasn't easy to decipher.


    Edit: Since your last edit made more sense, I assume this is what you really wanted:
    [bfn]=INDEX(B1:B45,MATCH("*"&A1&"*",B1:B45,0),1)[/bfn]

    Re: Find Text In A String Of Text And Return Whole Found Text


    Assuming "short text" is in column A and "long text" is in column B, this formula should give your desired results:
    [bfn]=IF(ISNUMBER(SEARCH(A1,B1,1)),B1,"not found")[/bfn]

    Re: Textbox Password Complication


    Quote from ShawnIRQL

    I don't want the textbox to appear when hitting the button to hide


    I'm not sure what you are asking, but "PasswordBox.Show" is always going to be called, because you are testing if column is hidden imediately after you hide it. Is this what you want:

    Re: Find And Replace Old Data With New Data


    Try this:

    Code
    For Each cell In rng2
    On Error Resume Next
    rng1.Find(cell).Offset(0, 1) = cell.Offset(0, 1)
    If Err.Number <> 0 Then MsgBox cell.Value & " was not found in master list"
    On Error GoTo 0
    Next cell

    Re: Commanbutton To Close Active Workbook And Open Different Workbook


    Did you look in the help file to find the proper syntax for the Close method? I don't mind offering my help, but it would be nice if you were willing to put forth a little effort to find the solution on your own. Having said that, I assume this is what you need:

    Code
    Workbooks("test1.xls").Close

    Re: Running A Loop And Pasting Over Random Decimal Numbers


    Quote from sam000

    This only occurs when the source values in the excel spreadsheet are decimal numbers generated by the use of =RAND.


    If I had to guess, I'd say it only occurs when the number generated by Rand is less than .5. When the number is greater than .5, I imagine you get 1, not 0. Since your variable is an integer, and the number you are evaluating is always going to be between 0 and 1, you will always get either 0 or 1 when you convert to integer.

    Re: Getting Multiple Items From An Unsorted Table


    Quote from aslsw

    Thanks. It might come to that, but I would prefer something a bit more 'elegant' (no offence) that removes the blank lines.


    a) That's why I would use a filter.
    b) What blank lines? The ones you included in your example table?

    Re: Getting Multiple Items From An Unsorted Table


    Personally, I wouldn't use a Lookup function, I would just filter the Itinerary list for Nights > 0 and use the filtered list as my Accomodation list. If you have your heart set on using VLookups, this might be want you want:
    Cell C13 copied down: [bfn]=VLOOKUP(B13,$B$4:$F$9,5,FALSE)[/bfn]
    Cell D13 copied down: [bfn]=IF(C13=0,"",VLOOKUP(B13,$B$4:$F$9,4,FALSE))[/bfn]

    Re: Keyboard Shortcut Causes Error


    I'm stumped. Apparently, you can't use Workbook.Open in a macro called from keyboard shortcut. Don't quote me on that, but in my limited testing, I could never get it to work. If you add a custom button or custom menu, you can call macro from there.

    Re: Add Incrementing Letter To The End Of Each Cell In A Highlighted Range


    I was getting ready to sign out for the night, but all this talk of brilliance forced me to stay. Is that what you wanted John?

    Re: Count Unique Entries Within Variable Date Range


    Quote from Dave Hawley

    Dom, your formula returns 29, same as the DCOUNT?


    I thought DCount worked when I first looked at it too. However, it only works when your start date in the first date listed, i.e. 1/7/08. DCount fails for any other start date, whereas Dom's formula seems to work for any date range.