Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
| Search | Today's Posts | Mark Forums Read
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 26th, 2010, 03:01 AM
Registered User
 
Join Date: Mar 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old June 18th, 2010, 11:41 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi Vamshi

Can you try the following

Selection.Copy Destination:=Range("N11")

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 22nd, 2010, 08:08 AM
Registered User
 
Join Date: Mar 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This din't work, but i have solved the problem by using the following statement

ActiveCell.PasteSpecial




Similar Threads
Thread Thread Starter Forum Replies Last Post
Schedule a VBA macro to run at a specific time marshall04b Excel VBA 8 October 31st, 2015 08:41 AM
Copy visible cells in a macro?Runtime 438 error? nbaj2k Excel VBA 2 April 7th, 2010 11:13 AM
Run a macro at a certain / specific time Andy84 BOOK: Professional Outlook 2007 Programming ISBN: 978-0-470-04994-5 1 June 8th, 2009 05:19 AM
Schedule Excel to run a macro at a specific time and date SheriV Excel VBA 4 March 18th, 2009 04:12 PM
Schedule a VBA macro to run at a specific time peterlihh Other Programming Languages 0 October 24th, 2008 07:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.