+ Reply to Thread
Results 1 to 6 of 6

Modify my copy from one cell to another macro

Hybrid View

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    Sacramento, CA
    MS-Off Ver
    O365
    Posts
    70

    Modify my copy from one cell to another macro

    So, I got some code from the great people at this forum, and was wondering if it could be tweaked a bit.
    The coded copies txt from one field to another, and puts itself at the top of the cell and removes the txt from the previous cell.
    The problem is, if cells in the column are blank it has started to put a square symbol in, so when we autofilter it will show squares. I can work around it by filtering for the squares and deleting them manual which is still a time saver. The problem is if the squares get put in between other txt there's no way to delete them. I can live with it, but figured it would be worth asking, as it did not always do this.

    Thanks in advance

    Sub AJ2AI()
        Dim cell As Range
        Dim s As String
        Dim r As Range
        Dim sFrom As String
        Dim sTo As String
        Dim lFrom As Long
        Dim lOffset As Long
        Dim rRange As Range
        Dim rTop As Range
        Dim rBottom As Range
        Dim sh As Worksheet
    
        sFrom = "AJ"
        sTo = "AI"
    
        On Error GoTo EF
        Application.EnableEvents = False
    
        lFrom = Range(sFrom & 1).Column
        lOffset = Range(sTo & 1).Column - lFrom
    
        Set sh = ActiveSheet
        With sh
            Set rTop = .Cells(2, lFrom)
            Set rBottom = rTop.Offset(.UsedRange.Rows.Count - 1)
            Set rRange = Range(rTop, rBottom)
        End With
    
        For Each cell In rRange
            With cell
                Set r = .Offset(0, lOffset)
                s = r.Value
                If InStr(s, .Value) = 0 Then
                    s = .Value & vbLf & s
                    With r
                        .Value = s
                        .VerticalAlignment = xlTop
                    End With
                    .Value = ""        'remove this line if you want column(sFrom) to remain as is.
                End If
            End With
        Next cell
    
    EF:
        Application.EnableEvents = True
    End Sub
    Last edited by arcsum68; 09-16-2010 at 01:28 PM.

  2. #2
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: Modify my copy from one cell to another macro

    Replace the line
    s = .Value & vbLf & s
    with
    If (s <> "") Then
       s = .Value & vbLf & s
    Else
      s = .Value
    End If
    Bob
    Click my star if my answer helped you. Mark the thread as [SOLVED] if it has been.

  3. #3
    Registered User
    Join Date
    07-01-2010
    Location
    Sacramento, CA
    MS-Off Ver
    O365
    Posts
    70

    Re: Modify my copy from one cell to another macro

    That works better, what I did not realize is it puts that square in there during the automation so when you drop the autofilter you see it. If you alt+enter to add a new row you do not see that square. Is there anyway to prevent that?

    I have attached a screen cap
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    07-01-2010
    Location
    Sacramento, CA
    MS-Off Ver
    O365
    Posts
    70

    Re: Modify my copy from one cell to another macro

    Nevermind, guess there is a square even if you do it manually. I don't remember that being the case previously.

    Solved! Thanks that will help me keep things nice and clean.

  5. #5
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: Modify my copy from one cell to another macro

    The square is the present of a nonprintable character. If you want the concatenated text to appear on different lines within the cell, you will have to put up with it on the autofilter. You could use a different delimiter, like "|", but then the text would all appear on the same line.

  6. #6
    Registered User
    Join Date
    07-01-2010
    Location
    Sacramento, CA
    MS-Off Ver
    O365
    Posts
    70

    Re: Modify my copy from one cell to another macro

    I am fine with it, but I have someone who was resistive to the macro's when I first started using them despite the HUGE amounts of time they saved. There were issues that were being blamed on the macro's so I try to keep them as clean as possible. The version you corrected left room for error, but its all good now.

    Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1