INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA code won't run

VBA code won't run

(OP)
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?

RE: VBA code won't run

What is the text of the error message?

RE: VBA code won't run

(OP)


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

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)?

RE: VBA code won't run

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

RE: VBA code won't run

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

RE: VBA code won't run

(OP)
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.

RE: VBA code won't run

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.

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 With 

Check out our sister site, www.Tek-Tips.com, where there are forums specifically designed for Microsoft office application issues.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA code won't run

(OP)
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?

RE: VBA code won't run

That sounds like the origin of your previous error messages.

RE: VBA code won't run

(OP)
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!

RE: VBA code won't run

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA code won't run

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA code won't run

(OP)
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

RE: VBA code won't run

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA code won't run

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close