Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Data (Excel 2003 sp3)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Hi,

    A while back (<post:=651,932>post 651,932</post:>) Hans helped me tremendously with this macro. This time, the second worksheet has many more columns, it goes all the way over to EF (136). I made a couple of changes to the macro you gave me last time, but I think it still needs to be tweaked. Using 'mreis' as the top worksheet (both mreis and mrec are present and open), I click in it, but when I press f5 to run it, I get a popup that says "Go To" at the top, and has a place at the bottom for me to put in a reference. I have no idea what goes there. Is it a filename or worksheet name, or maybe a cell reference? I just don't know.

    Here is what is in the macro:

    Sub MergeData()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim m1 As Long
    Dim m2 As Long
    Dim r2 As Long
    Dim c2 As Long
    Dim oCell As Range

    Set ws1 = Worksheets("mreis")
    m1 = ws1.Range("A65536").End(xlUp).Row
    ' **** fill in the name of the second sheet in the next line ****
    Set ws2 = Worksheets("mrec")
    m2 = ws2.Range("A65536").End(xlUp).Row

    ' Loop through rows of ws2
    For r2 = 2 To m2
    ' Do we find a matching name on ws1?
    Set oCell = ws1.Range("A2:A" & m1).Find( _
    What:=Trim(ws2.Range("A" & r2)), LookIn:=xlValues, _
    LookAt:=xlWhole, MatchCase:=False)
    If Not oCell Is Nothing Then
    ' If so, fill cells in the matching row, columns 2 to <font color=red>136</font color=red> (B to <font color=red>EF</font color=red>)
    For c2 = 2 To [/b]<font color=red>136</font color=red>[/b]
    If oCell.Offset(0, c2 - 1) = "" And Not ws2.Cells(r2, c2) = "" Then
    ws2.Cells(r2, c2).Copy Destination:=oCell.Offset(0, c2 - 1)
    End If
    Next c2
    Else
    ' Append entire row
    ws2.Rows(r2).Copy _
    Destination:=ws1.Range("A65536").End(xlUp).Offset( 1, 0)
    End If
    Next r2
    End Sub

    The only changes I made are indicated in <font color=red>red</font color=red>. The 136 used to say 14, and EF used to say N. I don't know what reference it is looking for or if I need to make additional changes.

    Any help is greatly appreciated.

    --Jim

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

    Re: Combining Data (Excel 2003 sp3)

    F5 can be used to run a macro from the Visual Basic Editor. In Excel itself, it is a shortcut for the Edit | Go To menu item, so you cannot use it to run a macro.
    To run the macro from Excel, you can do either of the following:
    - Select Edit | Macro Macros, select MergeData in the list of macros and click Run, or
    - Click the button I created on the mreis sheet (if it is present on the sheet you're using now).

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (Excel 2003 sp3)

    Hans, you are simply amazing! As much so, I'm a novice at this. Thanks for making it simple for me, and making is easier to understand.

    You Rock! <img src=/S/music.gif border=0 alt=music width=97 height=29>

    --Jim

Posting Permissions

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