Posts by firefytr

    Re: Copy & Paste Same Range Several Times.

    Hello there,

    Can you be a bit more specific about your exact data ranges? What if there is another date? Where would it be? Are they actual dates or textual dates? Do you wish to preserve formatting of these cells? These questions are important as a copy method may not even be needed here.

    Re: Overflow Error

    Change your Integer to a Long. It is more efficient anyway, since the computer will convert your Integer first to a Long, then back to an Integer, and there isn't anything an Integer can do that a Long cannot.

    Re: Skip A Worksheet When Protect Macro Runs

    Hi there,

    Something like this then...

    Sub Protect() 
        Dim sht as Worksheet
        For Each sht In ActiveWorkbook.Worksheets 
            If sht.Name <> "Your Sheet Name Here" Then sht.Protect "OJT"          
    End Sub


    Re: Autogenerating Case Numbers For A Vba Form

    Hi there,

    If you want to use a formula, enter the starting number in A3, then in A4 enter the following...


    Then copy the number down.

    If you are wanting a larger increment then you'll need to specify how you would like to do so, or if you are wanting to use text (non-recognized numbers) you'll need to also specify that system as well.

    If you can do this with formulas rather than VBA it would be better. Formulas will generally and usually be faster than VBA. Plus they are easier to maintain/troubleshoot.


    Re: Copy Rows Matching Criteria

    You can replace all of this code...

    .. if you just use a simple function to test for a sheets existence. This is an example...

    Then just use in your code like such..

    'dimension variables at start of code...
    Dim wb as Workbook, ws as Worksheet
    Set wb = Activeworkbook
    'If SheetExists("Numbers", wb) = False Then
        Set ws = wb.Worksheets.Add(after:=wb.sheets(wb.sheets.count))
        set ws = wb.sheets("Numbers")
    End If
    '... rest of code


    Re: Check If Workbook Is Open

    I always like to test with a separate function, which really only comes down to two lines. Here is what I use...


    Re: Add Row If 2 Columns Have Certain Values

    The * acts as a logical AND operator. Stepping through the calculation with the formula auditor will give you a better idea about it. There are many resources on the web if you do a Google search as well. It partly acts as a boolean coercion (from boolean to integer values, TRUE becomes 1 and FALSE becomes 0).


    Re: Copy and Paste Macro

    To leave them as values, you must select the entire range again, press Ctrl + C, then press Alt + E, S, V (that is press and hold Alt, press the E key, let go of both, then press the S key, then press the V key), then Enter to confirm.


    Re: File opens whenever I start Excel

    Could be that there is no Activeworkbook. Try this ..

    If Not ActiveWorkbook Is Nothing Then
        If ActiveWorkbook.Name <> "GeotechDataEntryTemplate.xls" Then Exit Sub
    End If

    Also, check Tools | Options | General (tab), is there anything in the startup textbox?

    Re: Converting column numbers to letters (and a little more)

    Your ConvertToLetter will fail after column Z. You can use something like this if you'd like for all columns ..

    Takes a range or [column] number.


    Edit: Pasted earlier version, apologies.
    (Heya Andy!)

    Re: count rows when worksheet is filtered

    Combat this by Defining the following names ...

    Refers to: =REPT("z",255)
    Refers to: =Bau!$F$2:INDEX(Bau!$F:$F,MATCH(BigStr,Bau!$F:$F))
    Refers to: =Bau!$G$2:INDEX(Bau!$G:$G,MATCH(BigStr,Bau!$G:$G))

    That makes the range dynamic and keeps any subsequent function running fairly optimally, only checking the range being used. Caveat? You can't put any other data below, not being used, in those columns. And you can't use numbers, at least not as the bottom-most row, as it won't pick those up.

    Also, the ShowAllData method will fail out if all of your filtered data already is at the All setting. You can bypass that by using an On Error Resume Next statement to test for this condition..

    Sub ViewAll()
        On Error Resume Next
    End Sub

    The only other thing the error could be is that there is no "Bau" sheets. While we know this is not the case, it is possible.

    If you wanted to - although I don't know why - you could manually step through each field and reset it's filter ..

    Sub ViewAll_Ver2()
        Dim i%
        For i = 1 To Sheets("Bau").AutoFilter.Range.Columns.Count
            Sheets("Bau").AutoFilter.Range.AutoFilter field:=i
        Next i
    End Sub


    Re: Column number of cell from address

    Returning the column letter(s) is a different situation. I generally try to use something like this ..

    Left(c.Address(True, False), InStr(1, c.Address(True, False), "$") - 1)

    Edit: Sorry, where "c" is the range in question. I.e. ..

    Dim c As Range
    Set c = Range("AZ100")