Solved

Code doesn't like to work together

Posted on 2014-01-10
7
404 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

729 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