Posts by turtle44

    Re: Makro Exel Lotnumber


    If you need a macro, let me know, but I think a formula would be easier. Try this:


    =DATE(2000+MID(A5,2,2),MID(A5,6,2),1)


    Then you can format this cell as date.

    Re: Formula References Not Updating On Row Insert/Delete


    I didn't really look at the code all that closely, but here is a theory I have. Let me know if I am way off.


    Your original formula was =Sum(E3:E11)
    Let's say, for example, that you then delete 5 rows, so your new formula is =Sum(E3:E6)
    Then you add rows. If you add rows between row 3 and row 6, your formula will adjust correctly. If you add rows between row 7 and 11, your formulas would not include the new rows.


    Could that be the cause of your problem.

    Re: Deleting Rows With A Macro


    Please use code tags when including code in your post.


    Try this:

    Code
    lLastRow = Range("A65536").End(xlUp).Row
    For i = lLastRow To 2 Step -1
       If Cells(i, "A") Like "*aol.com" Then
          Rows(i).EntireRow.Delete
       End If
    Next i

    Re: Screen Updating and Macro Error


    Application.ScreenUpdating = False will not prevent these messages from appearing; if you want to suppress these messages, use Application.DisplayAlerts = False. Be sure to use Application.DisplayAlerts = True at the end of your code though.

    Re: Extract Alphanumeric String After Hitting First Alpha Character


    This is at least your third post this week regarding getting info from alpha-numeric strings. Have you tried modifying the code you received in response to your other posts? I'm not trying to be a jerk, it just doesn't appear that you are putting much effort into solving the problem yourself.

    Re: Check Cell Value Length And Uniqueness Before Submission


    Here is a basic outline of the code you will need. You could also use data validation to test for either of these conditions (but not both, at least not to my knowledge) upon data entry into cell.


    Re: Desire Versatile Alphanumeric Extractor From Any String


    Is this what you need?


    Re: Finding Text on Varying Workbook "b", Returning 10 Cells Under The Given Text To "a"


    This bit of code will copy a 10X10 range of cells starting one cell below the first cell containing the word "Within" on the active worksheet. Is that what you need?



    Code
    Dim Cell As Range
       Dim rng As Range
    
    
       Set Cell = Cells.Find(What:="Within", After:=ActiveCell, LookIn:=xlFormulas, _
             LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
       Set rng = Range(Cell.Offset(1, 0), Cell.Offset(1, 9))
       rng.Copy

    Re: Cell Displays Formula Not Result


    Problem solved. Thanks for the replies.


    I was inserting the formula in a newly added column (the cells in the new column were formatted as text). After I added the formula, I formatted the cells as General, but it was apparently too late. When I changed the code to format the new column as General before inserting the formula, everything worked fine. Thanks again.

    I have a macro that is attempting to insert a UDF into the used range of column B, then replace the formula with values. Here is a portion of the code:


    Code
    With rng
       .FormulaR1C1 = "=ConvertName97(RC[-1])"
       .Copy
       .PasteSpecial (xlPasteValues)
       Application.CutCopyMode = False
    End With


    The problem is that the cells in rng display "=ConvertName97(RC[-1])" (without the quotes) instead of the calculated value. If I type the UDF directly into the cell, or if I use the Insert Function Wizard, I get the same results (the Formula Result bar in the Function Wizard gives me the correct value, but the cell still displays the formula).


    The only way I can get it to work is the put the formula into another cell in my workbook (either by entering it manually or using the wizard) and then copying it to my range.


    I have verified (several times) that Calculation is automatic, and I have also recalculated the sheet. I have also verified that View Formulas is not checked. I am probably overlooking something incredibly simple, I just have no idea what it could be. Any help would be greatly appreciated.