Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Assistance with Formulate Macro (Excel 2002)

    Hi guys,

    Could someone help me get this macro running properly. Its not pasting the data in the correct position, and blank lines are not deleting!
    When you open the attachment, you will see how I want the macro to set up the data.
    Thanx for your assistance!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance with Formulate Macro (Excel 2002)

    You can't test cell.Value = Empty, test if it is "" instead. Empty is used to test if a variable has never been initialized.
    When deleting rows in a loop, you must loop backwards to avoid pulling the rug from under the loop.
    You must move the "Chinese proverb" etc. text one row UP, so the row offset must be -1 instead of 1.
    You must delete the row that contained this text as well, since it becomes blank.
    You cannot use Trim(cell).Cut ..., for Trim(cell) results in a string, not in a range object.

    Modified code:

    Sub Formulate()
    Dim lngMaxRow As Long
    Dim lngRow As Long
    Dim currCell As Range
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 1 Step -1
    Set currCell = Range("A" & lngRow)
    If Left(currCell.Value, 1) = "-" Or currCell.Value = "" Then
    currCell.EntireRow.Delete
    ElseIf Left(currCell.Value, 1) = " " Then
    currCell.Value = Trim(currCell.Value)
    currCell.Copy Destination:=currCell.Offset(-1, 1)
    currCell.EntireRow.Delete
    End If
    Next lngRow
    Set currCell = Nothing
    Columns("A:B").AutoFit
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assistance with Formulate Macro (Excel 2002)

    Many millions of <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> to you!
    Thanks for straightening that up! It's interesting to note how you work from the bottom upwards...I see how it makes sense if you are deleting the "current" row you are in.
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Assistance with Formulate Macro (Excel 2002)

    you could use:

    IsEmpty(currCell.Value)

    if you want to distinguish between a truly empty cell and a null string (which are not the same)

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance with Formulate Macro (Excel 2002)

    That's true, but I don't think it's important in this situation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •