Link to home
Start Free TrialLog in
Avatar of BostonBob

asked on

Code doesn't like to work together

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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Does the tab name Blue exist in the workbook? Does the spelling match exactly?

What is the error displayed?

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"

Avatar of BostonBob


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)

Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sweet!  It worked.   A+