Posts by Richie(UK)

    Re: Need for Speed


    Probably won't have a huge impact but you could try switching off Screenupdating and switching Calculation to manual at the start of the routine and then switch back again at the end.

    You could also declare the appropriate types for your FSO objects rather than leaving them as Variants (every little helps!)

    Re: Deleting entire rows with certain words

    Quote from imverynew;615190
    ... I need an owner that has LLC or INC or LP deleted so we dont send them mail. ...

    I can't see any of those designations in the details of column A (Owner). Is the example not representative or should we be looking elsewhere - eg column L (Operator)? Either way the simplest approach would be to add a column with a formula to identify the desired cells and then act based upon the contents of this added column. The deletion process itself is then fairly straightforward (using Autofilter, or Find etc). Alternatively, you could just filter and not delete anything.

    By the way, I hope this is fictional data and not real data otherwise you may find yourself in deep water! (people don't appreciate having their name and address details made public, especially on the internet). If it's real data then I'd recommend that you remove it. Just athought ...

    Re: Method 'Range of objects '_Worksheet' failed error

    Hi and welcome to the forum.

    I don't entirely follow what you are trying to achieve but give this amended version a try and we'll take it from there. Any errors? Expected results?

    Re: Assign cell value to array


    1. Please ensure that you always use code tags when posting code.

    2. Where have you defined the variable 'c'? How can it be selected when Excel doesn't know what it is? Why define it as a Variant rather than a Range? How is the cell supposed to change on each pass of the loop?

    Re: Excel 2000 very slow network save speed to Windows 2008 Server

    Might the answer be to re-work the menus so that they are compatible with later versions of Excel? (Speaking theoretically here as I only have 2003 on this PC!)

    Re: VBA code not retrieving any data

    Chr$(34) is the equivalent to double quotation marks.

    strVariable = "Hello" will return Hello (without quotes)
    strVariable = Chr$(34) & "Hello" & Chr$(34) will return "Hello" (with quotes)

    Alternatively you could use three lots of double quotation marks either side.

    strVariable = """Hello""" will return "Hello" (with quotes)

    The issue usually arises when you have variables with spaces in them. So, 'Hello' would be fine but 'Hello World' would need the double quotes. I had a similar issue a while back when working with WinZip files. I introduced the following line to work around it:

    'If source name has one or more spaces surround it with ""
    If InStr(1, strSource, " ", vbTextCompare) <> 0 Then strSource = Chr(34) & strSource & Chr(34)

    Anyway, hope that helps to clarify the situation.

    Re: Creating a License Acceptance Dialog Sheet

    woodman, I've fixed the code tags for you this time as I can see that you made an attempt to use them (please persist next time though until you get it right :) )

    Are you attempting to modify code that you found somewhere on the net or in an old workbook? The reason that I ask is that in addition to Roy's observation I see that you are using 'Auto-Open'. This, too, is only supported at the moment to offer some backwards compatability - you should be using the Workbook_Open event instead.

    Re: VBA code to hide/unhide sheets in a dynamic named range

    Well, I just downloaded your example workbook and used Wigi's suggested routine (amending the Visible status as per post #9) and it works exactly as expected. That is, there are 3 worksheets called 'Monthly Summary', '1', and '2' and a named range called "weekdays_in_current_month_range". Within the named range there is a '1' but not a '2'. Therefore, when the routine is run the sheet called '1' is hidden and the other 2 remain.

    This suggests to me that either the workbook that you are using is different to the example given or that you have modified the suggested routine and in doing so have inadvertently prevented it from working as intended. You will need to check the workbook for yourself but if you post the exact routine that you are using I'm sure that somebody will point out any glaring errors.

    Re: Just introduced to VBA: insert rows (random for each file: about 500 files)

    Hi and welcome to the forum.

    I think we're going to need some more information from you. What, specifically, determines the point at which a new row should be inserted? What determines whether the mean should be of 20, 25, or 30 rows? It may be simplest if you upload a workbook (remember to delete/disguise any personal details) showing the data and an example of what you are trying to achieve.

    Re: Filter same column by 9 criteria


    I don't think that you can do it insitu, only by copying to another sheet as you suggest. Not sure how far you have got with your coding but I have a routine that I put together a while ago that you may be able to adjust to suit your purposes. It filters the data in column A of Sheet1 using the criteria given in the Constants, it merges the filtered ranges using Union, and then copies the merged range to Sheet2.

    Re: Lat Long conversion

    Hi and welcome to the forum.

    Have you tried using 'Text to Columns' from the Data menu? That should split a cell containing N 30 31.80 W 90 56.30 into 6 columns : N, 30, 31.8, W, 90, and 56.3. You can then apply your conversion formula to the relevant cells. Would that work?

    Re: Run macro when user cancels file close.


    Are you talking about the scenario where the workbook has changed and Excel wants the user to decide whether to save or not? You could try something like:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Me.Saved = False Then
            MsgBox "Hey, the workbook has changed!!!"
            'do something
            Me.Saved = True
            'close anyway
            MsgBox "Nothing to save - Bye"
        End If
    End Sub