Posts by tvsmvp

    Why does Access apend addl quote marks to records I cut/paste into say, notepad or excel? I don't seem them in access - but bang, there they are when I cut and paste. Each is supposed to start with a single quote mark (and then of course end with one) - but not two on each side. I've tried stripping them out via notepad, but that doesn't work, so apparently access is taking it upon itself to add one quote for each quote.


    I've got a list of a thousand keywords/phrases. I've got (let's say) ten queries written to each pull all instances of a particular word (from w/in the master list of phrases). How can I create a final query that pulls together all phrases not included in the other queries?

    In other words, I’ve got a list of a thousand keywords. The first ten queries strain out 800 of the entries, how do I produce a list of the remaining two hundred?

    I've tried everything I can think of - But it just seems like there oughta be a simple solution.

    Regards - Keith

    Two Things: One, I bought Dave's Excel Hacks book - and it rocks. They should charge more, if you ask me.
    Two, this is probably been asked a million times - but how would you access a named range in a closed workbook? In order for the in-cell dropdown to work, the other workbook has to be open. Is there a way around this? Basically, I have half a dozen people, each on their own workbook. Each of these workbooks should read a list from a common (most likely closed) workbook, so that I can keep all my (common) data in one place.

    thanks - !

    Andy Pope, to answer your questions: This workbook does use hidden sheets and it's as if the compute is painting the page with lots of mirror images of the calendar - like the trailing mouse pointer Babarr mentions. I tried setting screenupdating to false in Sub UserForm_Activate() - but to no avail. Also, I've tried Barbarr's demo - but can't seem to replicate this effect with his book. Hm....

    I placed the code in my own workbook. This happens in both Excel 2000 and 2002. It also happens with both version 9 and 10 of the calender control. It will not happen if I simply "play" the calender (you know, hit the arrow/"play" key from w/in the vba editor and pop over to Excel proper. It doesn't happen in other books when I simply add this control to an otherwise blank book, leading to the obvious conclusion that it's something messed up in mine. It may be, I'm guessing, that my workbook is so large that the computer's memory just gives up trying to "repaint" the screen. I'm afraid I can't post it, too much info in it - and, as I said, putting the calender into another, test excel sheet, doesn't produce the same mystery blur.

    It only happens with the Workbook Dowload from the site, not with others I've created myself. I take it, it doesn't happen with your copy?

    I put in this:
    Private Sub CheckBox1_Click()
    Application.ScreenUpdating = True
    End Sub

    - but it doesn't seem to make a difference. Any suggestions?

    I'd like to run a macro any time a cell is changed on a sheet - but of course when you hit return the "selected" cell falls one below the cell I'd like the macro to effect. And, of course, they can simply click anywhere on the sheet. Is there a simple way to tell excel to execute the code before moving - or must I track (in the "worksheet activate" code) each and every cell somebody clicks in, in order to call it back, if in fact it's in need of this macro?

    Some time ago I took advantage of the pop-up calender that Dave included in one of his mailings. Naturally it works great - except I find that when you move the calender (by clicking in the blue bar across the top and dragging when visible), the calender becomes a giant blue blur. It's pretty to look at - but a little disconcerting, nonetheless. Is there a way to make it stop doing that? I tried turning off screen updating to no avail. Any suggestions?

    Actually, I'm filling across columns - but it's basically the same. Would I be correct in assuming that you use the select case vs a Do While loop because it's faster? EG:
    Do While Weekday(myFirstDate, vbMonday) > 5
    myFirstDate = myFirstDate + 1
    In order to check for company holidays, would you suggest setting up a "find" to simply check the list?
    Your help is much appreciated -

    Is it possible to use the workday function in vba, as opposed to being forced to use it in a cell as part of a formula? I'm trying to fill a row of cells with consecutive workdays (taking into account our holidays) with vba because placing the formula there (via vba) made the computer slowdown. Any thoughts? Any workarounds I've come up with employ too many "ifs" and "thens" and slow the calc down even more.

    In order to reduce the amounts of loops I'm asking my computer to perform, I'm trying to count the number of cells in a range that contain ";" – those will be the cells I'll need to work with. When I use this: "iLoop = WorksheetFunction.CountIf(Columns(1), ";")" it balks – because it wants my search criteria to match the entire cell contents. Any suggestions on how to count cells containing this character w/out resorting to "if"?

    If you put "a" in A10, "b" in B11 and "c" in A12, and run this sub (below), you'll get what I'm looking for, which is "a" in A30, "b" in A31 and "c" in A32. A relatively simple bit to write, yes – but having learned cool ways of doing things with each newsletter I receive, it occurred to me that there might be a "one-line solution" to this. I don't know, maybe something along the lines of: " Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value" which I found on your "speed up code" page.

    As a side question, you'll note that I've commented out the special cells line – because I found that filled cells are skipped when I do. Why would it skip certain cells even when I can see they've been selected?

    Sub MoveToColumn()
    c = 1
    r = 30
    For i = 1 To Selection.Count
    Set mycell = Selection.Cells(i)
    If Not IsEmpty(mycell) Then
    Cells(r, c).Value = mycell
    r = r + 1
    End If
    Next i
    Cells(r - 1, 1).CurrentRegion.Select
    End Sub

    (Oh, and if you've already included this in one of your emails, apologies - but you know it gets trying to find one small scrap of info)
    Thanks again!

    Normally I tear the books and online help apart looking for the answer before I turn to the forum... This time I thought I'd reverse that, as I'm thinking there might be an obvious answer already out there:

    I want to sort (and simultaneously combine into a single column) all the (filled) cells found in several rows (of varying lengths). In other words, it looks like a checkerboard and I want to throw all the data into a single column.

    I'd start by selecting special cells (formulas) - but is there a simple way to then dump all the contents a single column? - or is it just a matter of "crafting this together" in VBA?

    Hey, that works! Thanks! I had orignally worked through a similar thought, that the fractional numbers were messing things up – but I abandoned that line of thinking when I found that it had no problem when I would physically type in ten, fifteen or twenty numbers to the right of the decimal. Truncating works – even when I set it to 15 "fractional numbers."

    (I like the idea of using the "now" function to generate (via vba) a unique tracking number - as it has the added bonus of telling me when a particular project began.)

    FYI, I found that a list box, for some reason, has no problems like this whatsoever (ie, you don't have to use trunc, it works right out of the box as long as you multiply the members of your list by one (*1) as outlined in a previous post.

    Thanks for the help - it's always appreciated.