Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
Do I have something up-stream of the copy lines (both which error out) to make them fail?
Code is in a standard module I have seven sheets commented out as I test the code. Thanks, Howard Option Explicit Sub ZeroOneDashIandN() Dim c As Range Dim i As Long Dim j As String Dim MyArr As Variant Dim lr As Long Dim rngB As Range MyArr = Array("01-IN", "02-IN", "03-IN") ', "04-IN", "05-IN", "06-IN", "07-IN", "08-IN", "09-IN", "10-IN") Application.ScreenUpdating = False For i = LBound(MyArr) To UBound(MyArr) With Sheets("Sales Forecast") j = Range("B3").Value lr = Cells(Rows.Count, 11).End(xlUp).Row Set rngB = Range("B13:B" & lr) For Each c In rngB If c = j Then c.Offset(, 2).Resize(1, 76).Copy Sheets(i).Range("B" & Rows.Count) _ .End(xlUp)(2).PasteSpecial Paste:=xlPasteValues 'Sheets(i).Range("B" & Rows.Count) _ .End(xlUp)(2) = c.Offset(, 2).Resize(1, 76) End If Next 'c End With Next 'i Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
On Tuesday, December 10, 2013 10:49:56 AM UTC-8, Howard wrote:
Do I have something up-stream of the copy lines (both which error out) to make them fail? Code is in a standard module I have seven sheets commented out as I test the code. Thanks, Howard Option Explicit Sub ZeroOneDashIandN() Dim c As Range Dim i As Long Dim j As String Dim MyArr As Variant Dim lr As Long Dim rngB As Range MyArr = Array("01-IN", "02-IN", "03-IN") ', "04-IN", "05-IN", "06-IN", "07-IN", "08-IN", "09-IN", "10-IN") Application.ScreenUpdating = False For i = LBound(MyArr) To UBound(MyArr) With Sheets("Sales Forecast") j = Range("B3").Value lr = Cells(Rows.Count, 11).End(xlUp).Row Set rngB = Range("B13:B" & lr) For Each c In rngB If c = j Then c.Offset(, 2).Resize(1, 76).Copy Sheets(i).Range("B" & Rows.Count) _ .End(xlUp)(2).PasteSpecial Paste:=xlPasteValues 'Sheets(i).Range("B" & Rows.Count) _ .End(xlUp)(2) = c.Offset(, 2).Resize(1, 76) End If Next 'c End With Next 'i Application.ScreenUpdating = True End Sub Just to add, if I can, j value is actually on sheet("01-IN") cell B9. I tried this line in the code where it works on other examples but errors here. j = Sheets("01-IN").Range("B9").Value I had placed it just above the For i = LBound(MyArr) To UBound(MyArr) line. So now I have a formula in Sales Forecast range(B3) ='01-IN'!B9 Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
Hi Howard,
Am Tue, 10 Dec 2013 11:07:04 -0800 (PST) schrieb Howard: j = Sheets("01-IN").Range("B9").Value try: Sub ZeroOneDashIandN() Dim c As Range Dim i As Long Dim j As String Dim MyArr As Variant Dim varOut As Variant Dim lr As Long Dim rngB As Range MyArr = Array("01-IN", "02-IN", "03-IN") ', "04-IN", "05-IN", "06-IN", "07-IN", "08-IN", "09-IN", "10-IN") Application.ScreenUpdating = False j = Sheets("01-IN").Range("B9").Value For i = LBound(MyArr) To UBound(MyArr) With Sheets("Sales Forecast") lr = .Cells(.Rows.Count, 11).End(xlUp).Row Set rngB = .Range("B13:B" & lr) For Each c In rngB If c = j Then varOut = c.Offset(, 2).Resize(, 76) Sheets(MyArr(i)).Range("B" & Rows.Count) _ .End(xlUp)(2).Resize(columnsize:=74) = varOut End If Next 'c End With Next 'i Application.ScreenUpdating = True End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
Hi again,
Am Tue, 10 Dec 2013 20:12:07 +0100 schrieb Claus Busch: Sheets(MyArr(i)).Range("B" & Rows.Count) _ .End(xlUp)(2).Resize(columnsize:=74) = varOut sorry, typo. Change the line above: Sheets(MyArr(i)).Range("B" & Rows.Count) _ .End(xlUp)(2).Resize(columnsize:=76) = varOut Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
On Tuesday, December 10, 2013 11:15:42 AM UTC-8, Claus Busch wrote:
Hi again, Am Tue, 10 Dec 2013 20:12:07 +0100 schrieb Claus Busch: Sheets(MyArr(i)).Range("B" & Rows.Count) _ .End(xlUp)(2).Resize(columnsize:=74) = varOut sorry, typo. Change the line above: Sheets(MyArr(i)).Range("B" & Rows.Count) _ .End(xlUp)(2).Resize(columnsize:=76) = varOut Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 OH yea, works nicely. I see the omissions I made to set my code up to fail. Question on the: ..End(xlUp)(2).Resize(columnsize:=76) = varOut When you use columnsize:= does the : more or less Dim columnsize in place as a long? And could you use it like this if you had some rows also? ..Resize(rowsize:=10,columnsize:=76) = varOut Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
Hi Howard,
Am Tue, 10 Dec 2013 12:36:36 -0800 (PST) schrieb Howard: .End(xlUp)(2).Resize(columnsize:=76) = varOut When you use columnsize:= does the : more or less Dim columnsize in place as a long? columnsize (or rowsize) is a keyword. You don't need to dim it And could you use it like this if you had some rows also? .Resize(rowsize:=10,columnsize:=76) = varOut If I have to resize both dimensions I don't use the keywords. Then I write it as: ..Resize(10, 76) = varOut Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
Hi Howard,
Am Tue, 10 Dec 2013 21:42:13 +0100 schrieb Claus Busch: .Resize(rowsize:=10,columnsize:=76) = varOut have a look into the VBA help. I don't know how these words are called in the english language. In german language they are called "Named Parameters". And for these parameters the values will be assigned with ":=". The same you e.g. have with Find and What:=, Lookin:= and so on Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tried and true copy code lines fail me here
On Tuesday, December 10, 2013 12:59:17 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Tue, 10 Dec 2013 21:42:13 +0100 schrieb Claus Busch: .Resize(rowsize:=10,columnsize:=76) = varOut have a look into the VBA help. I don't know how these words are called in the english language. In german language they are called "Named Parameters". And for these parameters the values will be assigned with ":=". The same you e.g. have with Find and What:=, Lookin:= and so on Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 I wondered if columnsize was a key word, but thought it would appear as ColumnResize instead of all lower case. Thanks, I'll check it out. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy paste fail | Excel Programming | |||
Protecting worksheet cause vba code to fail | Excel Programming | |||
Changing macro security causes VBA code to fail | Excel Programming | |||
Placement of variables in called sub causes code to fail | Excel Programming | |||
Help on writing a code to copy and amend lines | Excel Programming |