Solved

Code doesn't like to work together

Posted on 2014-01-10
7
400 Views
Last Modified: 2014-01-10
Hi All,

I have some code that I have combined and I don't get why the vba compiler does not want to let it all work together.

Any thoughts?

Here is the code. The VBA compiler is having problems once it gets to the Worksheets("Blue") portions.   Even when I 'comment out' a line of Worksheets("Blue") portions then the next Worksheets("Blue") is having  the problem.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim r1 As String
r1 = Target.row

Dim a1 As String
a1 = Target.Address


If Not Intersect(Target, Range("Z10:Z1000")) Is Nothing Then

       Application.EnableEvents = False
       Application.ScreenUpdating = False

    Worksheets("Red").Range(a1).offset(0, 25).Formula = "321"
    Worksheets("Red").Range(a1).offset(0, 26).value = "0"
    Worksheets("Red").Range(a1).offset(0, 26).value = """"
    Worksheets("Red").Range(a1).offset(0, 24).value = Worksheets("Conversion").Cells(r1, "G").value
'    Worksheets("Blue").Range(a1).offset(0, 0).value = "Day"
  '  Worksheets("Blue").Range(a1).offset(0, -13).value = "Wait"
    Worksheets("Blue").Range(a1).offset(0, -12).Formula = "=Round(((Act!$T$4)/(Q!$AF$1011 + constants!$AT$3)) /(Red!AY" & r1 & "),0)"    
    Worksheets("Blue").Range(a1).offset(0, -11).value = "Engage"
    Worksheets("Blue").Range(a1).offset(0, -10).Formula = "=Round((constants!$M$2)*(Conversion!G" & r1 & "),Conversion!H" & r1 & ")"

    Worksheets("PreIniEntLong").Range(a1).offset(0, 3).value = "=Round(((Act!$T$4)/(Quotes!$AF$1011 + constants!$AT$3)) /(Red!AY" & r1 & "),0)"

    Call Sheet2.GoToYellow
   
 End If
 
  Application.ScreenUpdating = True
       Application.EnableEvents = True


End Sub
0
Comment
Question by:BostonBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39772116
Does the tab name Blue exist in the workbook? Does the spelling match exactly?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39772126
What is the error displayed?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39772130
If the tab Blue is in a different workbook you have to qualify the worksheet reference like this:

    Workbooks("My Other Workbook.xls").Worksheets("Blue").Range(a1).offset(0, 0).value = "Day"

Kevin
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:BostonBob
ID: 39772158
Thanks for the pronto response.

The error is:

run-time error '9': subscript out of range.


I shortened up the actual code in the example above.  In the real sheet that I have I have combined it with somebody else's sheet and their sheet for the "blue portion" is actually:

Blue Nevada   (from properties window)

I am assuming that VBA expects Blue_Nevada.
The actual sheet itself is sheet2     (from properties window)

thanks!
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 39772168
A worksheet has two names: the tab name and the code name. When using the Worksheets collection you use the tab name which has to match exactly the name on on the tab. So if the tab name is "Blue Nevada" then use that exact same text in the Worksheets collection:

    Worksheets("Blue Nevada")...

Kevin
0
 

Author Comment

by:BostonBob
ID: 39772201
Sweet!  It worked.   A+
0
 

Author Closing Comment

by:BostonBob
ID: 39772202
Great!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question