Posts by NateO

    Re: next without for error


    Change the order of End If and Next D.

    And change:

    If deletetenant = "Y" Or deletetenant = "y" Then


    If lcase$(deletetenant) = "y" then

    Re: speed up code


    In addition to suggestions posted here, I think you'd get better performance using the correct event, a Change Event. The trick is to only look at and modify cells that are in your Target range and their values have changed.

    I'm pretty sure I didn't get your conditions quite right, but here's an example:

    So, if only B39 changes, only B39 is looked at, versus looping through the entire range every time you make a selection change. ;)

    Re: CopyFromRecordset Object Range failed

    Hello Dennis,

    Thanks for following up. I'm only using the ADO stream to create a disconnected copy of a recordsest in that example, so I don't overwrite the original. But I'm passing null values to the Recordset before copying it to the sheet, so in that sense the Stream isn't relevent.

    I would assume that it really is an issue, but with XP and I can't recreate it and that article looks to be pretty dated (2001). Perhaps it was an issue with '97 and 2000. I do not know...

    Re: copying sheets to another workbook without VBA

    You could turn on the macro recorder and copy the chart individually, hide and adjust row heights, eh. Clean it up and add that code to the copy. ;)

    If this code is for you and not distributed, you could do something like the following:

    But if you're going to distribute the App, I would advise you not to do this due to the way XP handles programmatic access to the VBA projects (denied by default).

    Re: #div/0!


    Try using an if() call to test the denominator, e.g.,


    Dividing by 0 makes very little sense in any world that I know of.

    Re: copying sheets to another workbook without VBA


    Quote from ski52

    OK, long story short, my way didn't last. I tried the above but it doesn't copy all the nuances of the worksheet, only the data, and SOME formatting(why only some?). Can anyone else help?

    What kind of special, top secret formatting is being omitted? :?

    Re: Transpose addresses to rows array


    Try the following:

    Sub Tranny_Time()
    Dim cl As Range, myArr() As Variant
    Application.ScreenUpdating = False
    myArr = WorksheetFunction.Transpose(Range(Range("a1"), Range("a1").End(xlDown)))
    Range("b1").Resize(, UBound(myArr)) = myArr
    For Each cl In Intersect(Range("a2:a65536").SpecialCells(xlBlanks), _
    myArr = WorksheetFunction.Transpose(Range(cl(2), cl(2).End(xlDown)))
    Range("b65536").End(xlUp)(2).Resize(, UBound(myArr)) = myArr
    Application.ScreenUpdating = True
    End Sub[/vba]

    Re: Loop until row is empty

    The following should work on the active worksheet:

    Function sCase(ByRef strIn As String) As String
    Dim bArr() As Byte, i As Long, i2 As Long
    If strIn = vbNullString Then Exit Function
    Let bArr = StrConv(strIn, vbFromUnicode)
    Select Case bArr(0)
    Case 97 To 122
    bArr(0) = bArr(0) - 32
    End Select
    For i = 1 To UBound(bArr)
    Select Case bArr(i)
    Case 105
    If Not i = UBound(bArr) Then
    Select Case bArr(i + 1)
    Case 32, 33, 39, 44, 46, 58, 59, 63, 148, 160
    If bArr(i - 1) = 32 Then _
    bArr(i) = bArr(i) - 32
    End Select
    ElseIf bArr(i - 1) = 32 Then _
    bArr(i) = bArr(i) - 32
    End If
    Case 33, 46, 58, 63
    For i2 = i + 1 To UBound(bArr)
    Select Case bArr(i2)
    Case 97 To 122
    bArr(i2) = bArr(i2) - 32
    i = i2: Exit For
    End Select
    If bArr(i2) <> 32 And bArr(i2) <> 33 And bArr(i2) <> 46 _
    And bArr(i2) <> 63 And bArr(i2) <> 160 Then
    i = i2: Exit For
    End If
    End Select
    sCase = StrConv(bArr, vbUnicode)
    End Function

    Sub Sentence_Case()
    Dim cl As Range, clcMode As Long

    With Application
    .ScreenUpdating = False
    clcMode = .Calculation
    .Calculation = xlCalculationManual
    End With

    Columns("A:A").Font.Bold = True

    On Error GoTo ErrorTrap

    For Each cl In Range("a:a").SpecialCells(xlConstants, xlTextValues)
    cl.Value = sCase(LCase$(cl.Value))
    cl.EntireRow.Font.Bold = True
    Next cl


    With Application
    .Calculation = clcMode
    .ScreenUpdating = True
    End With
    End Sub[/vba]

    Edit: See attached text file for original tabbing. ;)

    Re: SFAS 123 Calculations

    Hello again Bob,

    The spreadsheet you're asking about can be downloaded here:

    If you open it in Excel, Excel will convert the Lotus Functions for you.

    However, note:
    1) I would have to guess they are assuming European-style options (which in many [if not most] cases is not true)
    2) The article and spreadsheet are 9 years old, very dated


    Term (in years)--the period between the date the options are granted and their expiration date

    Is questionable, if you read here:

    It says:


    Under the fair value based method, compensation cost is measured at the grant date based on the value of the award and is recognized over the service period, which is usually the vesting period.

    And vesting period and expiration date do not always equal. Food for thought... :)

    Re: SFAS 123 Calculations

    X^2 sqares X. It's an exponential symbol.

    That write-up is a little scary, while the FASB might be on board, it doesn't seem to acknowledge any difference between American and European style options; which will affect their value.

    It must be assuming one or the other...

    Re: SFAS 123 Calculations


    Looks like you're looking at Lotus 1-2-3 functions.

    Quote from

    Some functions, including mod, @VLOOKUP, and @HLOOKUP, are evaluated differently. For example, the @VLOOKUP function in Lotus 1-2-3 searches for an exact match in the first column; the VLOOKUP worksheet function in Microsoft Excel assumes the first column is sorted and finds the closest value in the first column that does not exceed the lookup value. The VLOOKUP and HLOOKUP worksheet functions in Microsoft Excel include a fourth argument, range_lookup. If you set this argument to False, Microsoft Excel searches for an exact match.

    You might want to use 'Excel' in your search.

    Here's an example I found:…p/gibson/blackscholes.htm

    But I can't speak to the accuracy of the approach, haven't used B.S. in a while. ;)

    Re: Listbox unique items from multiple worksheets

    Hello again,

    An advanced filter on a temporary range? Data->Filter...->Advanced Filter. To generate a column of unique items.

    ADO will not work on open workbook unless you want to generate a memory leak.

    Re: An Array of Specific Sheets

    Hi Lisa,

    There's no need to explicitly terminate ws, it goes out of scope following the loop. Shouldn't cause an error though.

    Can I see your whole block of code including the constant?

    Do all of these sheets exist?

    I can replicate that error with the following Constant:

    Public Const NW_ARR As String = "test,WA,OR,ID,MT"

    When sheet test does not exist.