Results 1 to 6 of 6

Thread: VBA error

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Set y2 = w2.Range("A" & x2 + 1)
    	
    	If y2 = Range("A2") Then
    		y2 = y2 + 1
    	End If
    
    ..........
    
    y1.Copy Destination:=y2

    The above code is inside a loop where I copy multiple datasets from various files into a master file, each one underneath the last. However, the headers are two rows merged into one, so on the first dataset, I need to add another 1 to take this into account. The "y2 = y2 + 1" is not performing as I would expect so "y1.Copy Destination:=y2" is throwing an error "Cannot change part of a merged cell".

    Can anyone see my fault?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    y2 = y2 + 1
    is just adding 1 to the value of y2. If you wanted to go down a row, then use:
    Code:
    Set y2 = y2.Offset(1, 0)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Bwaaaaah......

    I've tried lots of variations and cannot get this right.

    Please..... Open L1 and them import L2 followed by L3.

    L2 should import to cell A3, and then L3 should import directly below, but I am getting a blank row.

    Why?

    [attachment=84722:L1.xls][attachment=84723:L2.xls][attachment=84724:L3.xls]
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The problem is the test

    If y2 = Range("A2") Then

    This does not test whether y2 is the cell A2, but whether the value of y2 equals the value of A2.
    Since A2 is blank, and y2 is blank by definition (it is the cell below the last non-blank cell), the test will always be True.

    Replace the above line with

    If x2 = 1 Then

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans.

    y1.Copy Destination:=y2

    How can I do this as special values?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    y1.Copy
    y2.PasteSpecial Paste:=xlPasteValues

    or

    y2.Value = y1.Value

Posting Permissions

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