Type mismatch error on copying ranges.

Question

I am trying to make this work, but it says Type mismatch. Any help on what I am doing wrong? (I am very new to this)

Sub Copy_paste_XP()

Dim wsI As Worksheet
Dim aCell As Range, rngCopyFrom As Range, rng As Range
Dim lRow As Long

Set wsI = ThisWorkbook.Sheets("Move containers XP")

Set rng = ("E2:E500")

For Each aCell In rng
    If Len(Trim(aCell.Value)) <> 0 Then
        If rngCopyFrom Is Nothing Then
            Set rngCopyFrom = aCell
        Else
            Set rngCopyFrom = Union(rngCopyFrom, aCell)
        End If
    End If
Next

If Not rngCopyFrom Is Nothing Then rngCopyFrom.Copy

Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set rng = ("F2:F500")

For Each aCell In rng
    If Len(Trim(aCell.Value)) <> 0 Then
        If rngCopyFrom Is Nothing Then
            Set rngCopyFrom = aCell
        Else
            Set rngCopyFrom = Union(rngCopyFrom, aCell)
        End If
    End If
Next

If Not rngCopyFrom Is Nothing Then rngCopyFrom.Copy
Range("K501").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Which line is highlighted when it throws an error? Also, I see that you repeated your code *twice*, in essence. Can you explain as well what you want to do? I believe there is a much more efficient way. :) — WGS, Jan 13 '14 at 06:12
E2:E500 <---- This is selected for me. I am trying to make it copy e2:e500 and paste by value in K2 then copy f2:f500 and then paste them by Value in K501 while excluding 0's or blanks — Forbidden, Jan 13 '14 at 06:17
ah, you forgot to reset your `rngCopyFrom`. Add this line `Set rngCopyFrom = Nothing` before you start the next loop. If not, `rngCopyFrom` will contain both `E2:E500` and `F2:F500` range address which will trigger the error. You cannot copy multiple selection. — L42, Jan 13 '14 at 06:20
Same error... Maybe I am pasting it in the wrong spot? I am really sorry, I am very bad at this. I tried putting it under Next, set rng (2nd one) — Forbidden, Jan 13 '14 at 06:26
I understand what its trying to do, but when I reset it it still gives me that error — Forbidden, Jan 13 '14 at 06:31
try what Siddart pointed out in his answer :). That should fix it. Or re-write the whole thing using BK201's logic. :D — L42, Jan 13 '14 at 06:32
To reinforce @SiddharthRout's comment, kindly check [this](http://stackoverflow.com/questions/20738373/can-i-make-this-macro-more-efficient-or-faster/20754562#20754562) out. :) — WGS, Jan 13 '14 at 06:44

WGS · Accepted Answer · 2014-01-13T07:29:32.153

2

First off, you are not setting the variables correctly. You used Set rng = ("E2:E500") when it should be Set rng = wsI.Range("E2:E500").

Also, the macro can be made more flexible. The following code should work:

Sub CopyNotZero(SrcRng As Range, DestRng As Range)
    Dim Cell As Range, RngToCopy As Range
    For Each Cell In SrcRng
        If Cell.Value <> 0 And Len(Cell.Value) <> 0 Then
            If RngToCopy Is Nothing Then
                Set RngToCopy = Cell
            Else
                Set RngToCopy = Union(RngToCopy, Cell)
            End If
        End If
    Next Cell
    If Not RngToCopy Is Nothing Then
        RngToCopy.Copy
        DestRng.PasteSpecial xlPasteValues
    End If
    Set RngToCopy = Nothing
End Sub

Use it like this:

Sub Test()
    Dim wsI As Worksheet: Set wsI = ThisWorkbook.Sheets("Move containers XP")
    With wsI
        CopyNotZero .Range("E1:E500"), .Range("K2")
        CopyNotZero .Range("F1:F500"), .Range("K501")
    End With
End Sub

This will skip all cells with 0 value or no values at all.

Screenshots:

Set-up:

enter image description here

Result after running Test():

enter image description here

Hope this helps.

EDIT:

To call this macro everytime you paste to $A$2, the following code will work (modify accordingly):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        CopyNotZero Range("A1:A500"), Range("K2")
    End If
End Sub

Hope this helps.

edited Jan 13 '14 at 07:29

answered Jan 13 '14 at 06:28

WGS

13,969
4
48
51

Just curious... Why `On Error Resume Next`? – Siddharth Rout Jan 13 '14 at 06:30
@SiddharthRout: Oops. Also, forgot something. Editing now. – WGS Jan 13 '14 at 06:30
1

Also `RngToCopy.Copy DestRng` will fail if `RngToCopy` is nothing. You need the extra check `If Not rngCopyFrom Is Nothing Then RngToCopy.Copy DestRng` – Siddharth Rout Jan 13 '14 at 06:32
Thanks for pointing that out. Forgot that he wanted values as well, so I changed the above to use `PasteSpecial` rather than just `Paste`. :) – WGS Jan 13 '14 at 06:34
+ 1 it's all good now :) `Set RngToCopy = Nothing` can also move inside the IF/Endif ;) – Siddharth Rout Jan 13 '14 at 06:36
Thanks, @SiddharthRout! Wondering though. Which is best practice in this case, doing a catch-all and setting it outside, or include it in the loop since technically it hasn't existed anyway if the `IF` condition is not met? :/ – WGS Jan 13 '14 at 06:37
This worked great for me, thank you.One last question? If I wanted this to run automatically as soon as I put information in A2:A500 how would I approach that? Would it look something like this? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "A:2" Then Call MyMacro End If – Forbidden Jan 13 '14 at 07:24
@Forbidden: Check my edit above. You were pretty close, but your `.Address` is wrong, and you actually don't need `Call`. Just make sure the `CopyNotZero` macro is saved in a module in the same workbook. :) Kindly mark the answer as accepted if it helped you. Thanks! – WGS Jan 13 '14 at 07:30
Yeah I will, thanks again. I appreciate your patience with me! – Forbidden Jan 13 '14 at 07:36
No worries. This is also an exercise for me. Just look at how many times @SiddharthRout corrected me. Lol. :) Thanks for accepting and all the best. :) – WGS Jan 13 '14 at 07:52

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

E2:E500 <---- This is selected for me. I am trying to make it copy e2:e500 and paste by value in K2 then copy f2:f500 and then paste them by Value in K501 while excluding 0's or blanks – Forbidden 9 mins ago

Set rng = ("E2:E500")

You are missing the word Range. Change it to

Set rng = wsI.Range("E2:E500")

EDIT

Similarly for Set rng = ("F2:F500")

Also this seems to be a followup from your PREV question. I see you are still using .Select ;)

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 13 '14 at 06:24

Siddharth Rout

147,039
17
206
250

Haha yeah it is. You method worked for me, except for some reason it does copy 0's and for some other reason it pastes in K and L. – Forbidden Jan 13 '14 at 07:09

Type mismatch error on copying ranges.

2 Answers2