Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste not working in With statement - do I have too many 'With's?
Hi
For some reason, below is not executing properly in my code. The code finishes executing without error but does not actually paste (see the statement with "" below). FYI -- I've validated that the code goes through the loop where the Paste occurs (I put "Stop" after the '.cells.copy' statement -- the sheet clearly shows it has been copied). I'm wondering if I have one of the following issues (maybe both?). If I need to redesign that is fine (after I get it working I'm planning on attempting to use arrays instead of ranges), but there has been no error message. I want to make sure I understand why the Paste is not occurring first before making any changes. Thanks for your help in advance! 1 - Is there a limit to the number of With statements and/or embedded With statements ("With"s within "With"s) you can have? I have a bunch...there are ~35 "With" statements up through the below point in two subs that I'm executing back to back when a button is clicked (some of which have two layers deep of embedded Withs) 2 - I have a total of 26 variables (9 range variables, the rest with no variable type specified -- I believe they are variants by default?) up until this point in the code. There are another 13 variables that occur in two subsequent subs (3 ranges, 10 variants) that run back to back after the sub in questoin ends. I'm guessing this may be the issue as I've seen other posts where I believe it was said 30 variables or declarations) were the limit, but again, I do not get an error message. Code: With Sheets("Transaction Summary") .Cells.Sort Key1:=.Range("O2"), Order1:=xlAscending, Header:=xlYes, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal If Len(.Range("O3")) < 0 Then With .Range("O2", .Range("O2").End(xlDown)) .Formula = .Value End With .Cells.Copy With Sheets("Member ID Input Summary") .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Set checkrange = .Range("O2", .Range("O2").End(xlDown)) For Each H In checkrange If H.Value = H.Offset(1, 0).Value Then H.Offset(1, -2).Value = "Duplicate" Else End If H.ClearContents Next End With With Sheets("Transaction Summary") .Range("O2", .Range("O2").End(xlDown)).ClearContents .Cells.Sort Key1:=.Range("M2"), Order1:=xlAscending, Header:=xlYes, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End With Else End If End With ---- Robert |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
IF Statement not working | Excel Worksheet Functions | |||
IF statement not working | Excel Discussion (Misc queries) | |||
If statement not working | Excel Programming | |||
For Each Statement still not working | Excel Programming |