Posts by Richie(UK)

Re: Need for Speed

Hi,

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.

With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    '
    'Your code
    '
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

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

Re: Macro to load Solver in VBA ref

Hi,

I've read about this scenario before. See if this reference helps: http://peltiertech.com/Excel/SolverVBA.html (look at the "Using Solver in Excel VBA" section).

Re: Deleting entire rows with certain words

Quote from imverynew;615190

http://www.ozgrid.com/forum/showthread.php?t=60000
... 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?

Sub WorksheetLoop2()
     
     'Declare Current as a worksheet object variable.
    Dim Current As Worksheet
    Dim LastColumn As Integer
    Dim rng1 As Range, rng2 As Range
    
     
     ' Loop through all of the worksheets in the active workbook.
    For Each Current In Worksheets
        
        With Current
            
            If WorksheetFunction.CountA(.Cells) > 0 Then
         '       Search for any entry, by searching backwards by Columns.
                LastColumn = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
            End If
            
            Set rng1 = .Range(.Cells(11, LastColumn), .Cells(16, LastColumn))
            Set rng2 = .Range("Al17:Al22")
            
            .Range("A27") = Application.WorksheetFunction.CountIf(rng1, ">0")
            .Range("A28") = Application.WorksheetFunction.CountIf(rng2, ">0")
        
        End With
    
    Next
     
End Sub

Re: Run-Time Error 75 on Excel 2010/ Windows 7 while opening Text File

Hi and welcome to the forum.

I see that as part of the debugging process you have tried using an Excel file instead of the desired text file. Have you also tried with the (text) file being saved in a different location? For example, is there a Temp file that you could try?

Re: Assign cell value to array

Hi,

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

Quote from charley.003;614753

Excel 2007 saves the same xls file (in xls Excel 97 to 2003 file format) to Win 2008 Server in 0.5 seconds.

Excel 2000 saves the same xls file to local hard drive in 0.5 seconds.

Excel 2000 saves the same xls file to Win 2008 Severs in 11.5 seconds.

The problem is related to Excel 2000 saving to Win 2008 Server.

===

I cannot simply upgrade the work station to Excel 2007 because Excel 2007 is not compatible with the custom menus in the custom template/workbook. The only versions of Excel compatible with the menus are Excel 2000, Excel 2002, and Excel 2003.

Since Excel 2000 will not save fast to Win Server 2008, I am hoping Excel 2003 will save fast enough.

Display More

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: temporarily display userform

Pete, please remember to use Code tags when posting code to the forums. Thanks.

Re: Call Worksheet name by cell value

Hi,

How about simply:

Cells(LASTrow, 6).Value = Sheets(wsName).Range("K13").Value

Re: Finding the Last Changes in a Sheet

Hi and welcome to the forum.

Are you familiar with VBA/macros? I suspect the best approach here is going to be to make use of some Events programming. Something along these lines perhaps : http://www.ozgrid.com/VBA/track-changes.htm

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 FUNCTION that "looksup" data from an EXTERNAL workbook

Hi and welcome to the forum.

Please remember to use code tags when posting code to the forum (I've fixed your post for you this time).

Re: MsgBox not getting called

I think you are confusing the myInput and Response variables. Your first msgbox returns a value to the first variable but you are then testing the second.

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: workbook_open() not working - trying to allow grouping in locked sheets

Hi and welcome to the forum.

Two initial thoughts:
1. Is the routine within ThisWorkbook rather than a general module?
2. Have Events been disabled with code elsewhere?

Re: Filter same column by 9 criteria

Hi,

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.

Sub MultiFilter()
    
    Dim rngTarget As Range, rng1 As Range, rng2 As Range, rngMyRange As Range
    
    Const Crit1 As String = "Crit1"
    Const Crit2 As String = "Crit2"
    Const Crit3 As String = "Crit3"
    'the AutoFilter criteria to use
    
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        .Rows(1).Insert
        .Range("A1").Value = "dummy"
        'set up dummy header
        Set rngTarget = .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        'set the range to work with
        rngTarget.AutoFilter Field:=1, Criteria1:=Crit1, Operator:=xlOr, Criteria2:=Crit2
        'get first two criteria
        Set rng1 = .AutoFilter.Range.Offset(1, 0) _
            .Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        'range with first 2 (use offset and resize to exclude header)
        rngTarget.AutoFilter
        'reset autofilter
        rngTarget.AutoFilter Field:=1, Criteria1:=Crit3
        'get third criteria
        Set rng2 = .AutoFilter.Range.Offset(1, 0) _
            .Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        'range with 3rd
        rngTarget.AutoFilter
        .Rows(1).Delete
        'remove dummy header
        Set rngMyRange = Union(rng1, rng2)
        'combine the range
        rngMyRange.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A1")
        'or whatever you want to do ...
    End With
    Application.ScreenUpdating = True


End Sub

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.

Hi,

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
    Else
        MsgBox "Nothing to save - Bye"
    End If
End Sub