Solved

Code doesn't like to work together

Posted on 2014-01-10
7
392 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

830 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