VBA code won't run
VBA code won't run
(OP)
I'm having trouble with the following code throwing an error:
Where n is the loop variable in a For loop.
It think the problem lies with referencing Worksheets(2), but why?
Worksheets(1).Range(Cells(22, n + 2), Cells(23, n + 2)).Value = Worksheets(2).Range(Cells(4, n + 2), Cells(5, n + 2)).Value
Where n is the loop variable in a For loop.
It think the problem lies with referencing Worksheets(2), but why?





RE: VBA code won't run
RE: VBA code won't run
I've written the code differently to get the job done, but I'd still like to know what is causing the error.
RE: VBA code won't run
1) is there 2 worksheets? ( i think or is the first worksheet 0?)
2) Does all the cells have a value (not empty)?
RE: VBA code won't run
RE: VBA code won't run
RE: VBA code won't run
RE: VBA code won't run
RE: VBA code won't run
CODE
Dim ws2 As Worksheet Set ws2 = WorkSheet(2) With Worksheets(1) .Range(.Cells(22, n + 2), .Cells(23, n + 2)).Value = _ ws2.Range(ws2.Cells(4, n + 2), ws2.Cells(5, n + 2)).Value End WithCheck out our sister site, www.Tek-Tips.com, where there are forums specifically designed for Microsoft office application issues.
Skip,
for a NUance!
RE: VBA code won't run
So does anyone know why my original line of code doesn't work?
Also, I'm now having an issue with a change event. A certain change in a cell causes a For loop to run. Cells in a Range are changed one by one within this For loop. However, when these cells are changed, that is also an event, and that code tries to run before the For loop is finished. And that causes all sorts of problems (dividing by 0!).
I want the For loop to finish changing all the cells in the range before is executes the code for those cells changing. How would you do that?
RE: VBA code won't run
RE: VBA code won't run
I'm reading up on how to create class modules to control when events are raised... This appears to be a significant issue that someone who has actually be trained to write VBA probably would have already known about. But I'm just learning on the fly!
RE: VBA code won't run
The Cell Object defaults to the ActiveSheet. You must explicitly reference the Sheet to the Cell Object as per the posted code.
The error you got reflects an ambiguity in your code.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA code won't run
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA code won't run
Worksheets(1).Range(Cells(22, n + 2), Cells(23, n + 2)).Value = Worksheets(2).Range(Worksheets(2).Cells(4, n + 2), Worksheets(2).Cells(5, n + 2)).Value
RE: VBA code won't run
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA code won't run
1. Use named ranges
2. Read the source data into a variant array and work on that in VBA (see example below)
3. Write the modified data back to the worksheet in a single operation
For example:
Sub DoStuff()
Dim Data1 as Variant, Data2() as Double, n as long, numrows as long
Data1 = Range("Wks2NamedRange").Value2
numrows = UBound(Data1)
redim Data2(1 to numrows, 1 to 1)
For n = 1 to numrows
Data2(n1,1) = ... ' Do stuff to Data1
Next n
Range("Wks1NamedRange").Value2 = Data2
End Sub
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/