Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
with, end with
greetings, i'm using with and end with to copy a row, the with statement
specifying a worksheet. i need to do this operation over couple worksheets, and when i run macro there's a bug. doesn't seem to do instructions in with statement on sheets that aren't the active window. how can i correct this? here's code: Sub database() Dim oldr As Integer Dim newr As Integer Dim width As Integer oldr = Range("cCollar").Rows.count newr = Range("grid").Rows.count width = Range("grid").Columns.count If newr < oldr Then Worksheets("rating").Range(Cells(oldr + 3, 2), Cells(newr + 2, width + 1)).ClearContents Worksheets("output").Range(Cells(oldr + 3, 3), Cells(newr + 2, width + 1)).ClearContents End If If oldr < newr Then With Worksheets("rating") .Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2), Cells(newr + 3, 2)) End With With Worksheets("output") .Range(Cells(4, 3), Cells(4, width + 1)).Copy .Range(Cells(5, 3), Cells(newr + 3, 3)) End With End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
with, end with
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
with, end with
Mind your dots... you have this line...
With Worksheets("rating") .Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2), Cells(newr + 3, 2)) End With Where you have cells with no dot it is the same as Activesheet.cells and not Worksheets("rating").cells With Worksheets("rating") .Range(.Cells(4, 2), .Cells(4, width + 1)).Copy .Range(.Cells(5, 2), ..Cells(newr + 3, 2)) End With Should be better... -- HTH... Jim Thomlinson "mwam423" wrote: greetings, i'm using with and end with to copy a row, the with statement specifying a worksheet. i need to do this operation over couple worksheets, and when i run macro there's a bug. doesn't seem to do instructions in with statement on sheets that aren't the active window. how can i correct this? here's code: Sub database() Dim oldr As Integer Dim newr As Integer Dim width As Integer oldr = Range("cCollar").Rows.count newr = Range("grid").Rows.count width = Range("grid").Columns.count If newr < oldr Then Worksheets("rating").Range(Cells(oldr + 3, 2), Cells(newr + 2, width + 1)).ClearContents Worksheets("output").Range(Cells(oldr + 3, 3), Cells(newr + 2, width + 1)).ClearContents End If If oldr < newr Then With Worksheets("rating") .Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2), Cells(newr + 3, 2)) End With With Worksheets("output") .Range(Cells(4, 3), Cells(4, width + 1)).Copy .Range(Cells(5, 3), Cells(newr + 3, 3)) End With End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
with, end with
thanks, jim, thanks don, and thank god for this board! have a great weekend
everybody =D "Jim Thomlinson" wrote: Mind your dots... you have this line... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|