Please help: Run Time Error -40036 While running the macro for copy and paste
Hi,
I am getting this Error : Run Time Error 40036, when i try to copy a range of cells and paste in the same sheet at a different range in the same sheet.
could someone help me how to fix this error....
Sub countifdiffer()
Dim counter As String
counter = 0
flag = 1
K = 1
lRow1 = WorksheetFunction.Max(Range("E65536").End(xlUp).Ro w)
Set Inrange = Range(("E2:E" & lRow1)).SpecialCells(xlCellTypeVisible)(xlCellType Visible)
Selection.Copy
Range("N11").Select
ActiveSheet.Paste' Error at this point......
Application.CutCopyMode = False
For N = 1 To Inrange.Cells.Count
With Inrange.Cells(N)
val(N) = .Value
End With
With Inrange.Cells(N)
val1(N) = .Value
End With
Next N
For i = 1 To Inrange.Cells.Count
For j = i To Inrange.Cells.Count
If j = 1 Then
val2(K) = val1(j)
K = K + 1
GoTo skip
End If
If i <> j Or val(i) <> val1(j) Then
flag = lookup(val1(j))
If flag = 0 Then
val2(K) = val1(j)
K = K + 1
If K = Inrange.Cells.Count + 1 Then
GoTo completed
End If
End If
GoTo skip:
End If
skip:
Next j
Next i
completed:
For K = 1 To Inrange.Cells.Count
If val2(K) <> 0 Then
counter = counter + 1
End If
Next K
Cells(10, "I").Value = counter
MsgBox "counter is " & counter
Application.CommandBars(1).Reset
Erase val(), val1(), val2()
End Sub
Function lookup(val3) As Long
For z = 1 To Inrange.Cells.Count
If (val2(z) = val3) Then
lookup = 1
GoTo skip1
Else
lookup = 0
End If
Next z
skip1:
End Function
|
