Posts by firefytr

  • Copy & Paste Same Range Several Times.

    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.

  • Overflow Error

    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.

  • Skip A Worksheet When Protect Macro Runs

    Re: Skip A Worksheet When Protect Macro Runs


    Hi there,


    Something like this then...


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


    HTH

  • Autogenerating Case Numbers For A Vba Form

    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...


    =A3+1


    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.


    HTH

  • Copy Rows Matching Criteria

    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..


    Code
    '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))
Else
    set ws = wb.sheets("Numbers")
End If
'... rest of code


    HTH

  • Check If Workbook Is Open

    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...



    HTH

  • Add Row If 2 Columns Have Certain Values

    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).


    HTH

  • Fill Blank With Cell Above

    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.


    HTH

  • File opens whenever I start Excel

    Re: File opens whenever I start Excel


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


    Code
    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?

  • Converting column numbers to letters (and a little more)

    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.


    HTH


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

  • count rows when worksheet is filtered

    Re: count rows when worksheet is filtered



    Combat this by Defining the following names ...


    BigStr
    Refers to: =REPT("z",255)
    Imp
    Refers to: =Bau!$F$2:INDEX(Bau!$F:$F,MATCH(BigStr,Bau!$F:$F))
    Stat
    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..


    Code
    Sub ViewAll()
    On Error Resume Next
    Sheets("Bau").ShowAllData
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 ..


    Code
    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


    HTH

  • Column number of cell from address

    Re: Column number of cell from address


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


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


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

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