Solved

Code doesn't like to work together

Posted on 2014-01-10
7
364 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
  • 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now