VBA code won't run

[pperlich]

I'm having trouble with the following code throwing an error:

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?

 

[cowski]

What is the text of the error message?

 

[pperlich]

I've written the code differently to get the job done, but I'd still like to know what is causing the error.

 

[MortenA]

Two quick questions

1) is there 2 worksheets? ( i think or is the first worksheet 0?)
2) Does all the cells have a value (not empty)?

 

[pperlich]

Yes, there are 2 worksheets, and all the referenced cells in the range have numerical values.

 

[BigInch]

Are the name of the worksheets actually "Sheet1" and "Sheet2"?

 

[pperlich]

No, they have custom names, however, other places in the macro I have used Worksheets(1) and Worksheets(2) without issue. Not in the same line of code though.

 

[BigInch]

your code ran in my Excel 2013 without returning any error after, I changed it to "Sheet1" and "Sheet2" in accordance with the names of the sheets in my workbook.

 

[SkipVought]

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 With

Check out our sister site,

http://www.Tek-Tips.com,

where there are forums specifically designed for Microsoft office application issues.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pperlich]

Thanks for the help guys!

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?

 

[BigInch]

That sounds like the origin of your previous error messages.

 

[pperlich]

No because that code didn't exist yet.

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!

 

[SkipVought]

Please refer to the code I posted.

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!}

 

[SkipVought]

As far as the WorkSheet_Chande event firing each time your code creates a change in the sheet, you can use YourSheet.EnableEvents = False before the code for changes and the again after the changes, assign True.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pperlich]

Skip, that makes sense I think. So without using With as you did it should look like this?

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

 

[SkipVought]

You need to reference WorkSheet(1) with both Cells objects within the Range() object

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

A few suggestions that make life simpler (and calculations quicker):

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/