Posts by NateO

  • next without for error

    Re: next without for error


    Hello,


    Change the order of End If and Next D.


    And change:


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


    To:


    If lcase$(deletetenant) = "y" then

  • speed up code

    Re: speed up code


    Hello,


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

  • CopyFromRecordset Object Range failed

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

  • copying sheets to another workbook without VBA

    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:
    http://www.cpearson.com/excel/vbe.htm


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

  • #div/0!

    Re: #div/0!


    Hello,


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


    =if(b1,a1/b1,0)


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

  • copying sheets to another workbook without VBA

    Re: copying sheets to another workbook without VBA


    Hola,

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

  • Transpose addresses to rows array

    Re: Transpose addresses to rows array


    Hello,


    Try the following:


    [vba]
    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), _
    Range("a2:a65536").SpecialCells(xlConstants).Offset(-1))
    myArr = WorksheetFunction.Transpose(Range(cl(2), cl(2).End(xlDown)))
    Range("b65536").End(xlUp)(2).Resize(, UBound(myArr)) = myArr
    Next
    Range("a1").EntireColumn.Delete
    Range("a1:g1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
    End Sub[/vba]

  • Loop until row is empty

    Re: Loop until row is empty


    The following should work on the active worksheet:


    [vba]
    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
    Next
    End Select
    Next
    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


    ErrorTrap:


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


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

  • SFAS 123 Calculations

    Re: SFAS 123 Calculations


    Hello again Bob,


    The spreadsheet you're asking about can be downloaded here:
    Page:
    http://www.cpajournal.com/down.htm
    Spreadsheet:
    http://www.cpajournal.com/sfas123.wks


    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


    http://www.investopedia.com/features/eso/eso3.asp


    Quote


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


    Is questionable, if you read here:
    http://www.fasb.org/st/summary/stsum123.shtml


    It says:


    Quote


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

  • SFAS 123 Calculations

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

  • SFAS 123 Calculations

    Re: SFAS 123 Calculations


    Hello,


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


    Quote from http://support.microsoft.com/?kbid=179841


    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:
    http://www.trinity.edu/rjensen…p/gibson/blackscholes.htm


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

  • Listbox unique items from multiple worksheets

    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.

  • An Array of Specific Sheets

    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.