Multiple ranges selected with Union works half the time

[karenbluez]

I've got the following problem with my code on Excel 2007 (Must work however on 2003 for our legacy systems). I'm trying to get the ranges selected, and get the values in ranges to multiply by negative 1 to switch the signs around.

Every second time I run it I get an error message where it has to select the Budget range. (Error message: Run time error 1004, Select method of range class failed)

Any help will be super greatly appreciated!!!

    Set ws1 = Worksheets("Actual")
    Set ws2 = Worksheets("Budget")
    Set ws3 = Worksheets("Forecast")

    Set Actual_Rng = Application.Union(Range("C88:AM101"), Range("C103:AM114"), Range("C116:AM131"), Range("C143:AM156"), Range("C158:AM169"), Range("C170:AM186"), Range("C198:AM211"), Range("C213:AM224"), Range("C226:AM241"), Range("C253:AM266"), Range("C268:AM279"), Range("C281:AM296"))
    Set Budget_Rng = Application.Union(Range("C88:AL101"), Range("C103:AL114"), Range("C116:AL131"), Range("C143:AL156"), Range("C158:AL169"), Range("C170:AL186"), Range("C198:AL211"), Range("C213:AL224"), Range("C226:AL241"), Range("C253:AL266"), Range("C268:AL279"), Range("C281:AL296"))
    Set Forecast_Rng = Application.Union(Range("C88:R101"), Range("C103:R114"), Range("C116:R131"), Range("C143:R156"), Range("C158:R169"), Range("C170:R186"), Range("C198:R211"), Range("C213:R224"), Range("C226:R241"), Range("C253:R266"), Range("C268:R279"), Range("C281:R296"))
    
    ws1.Select
    ws1.Range("A1") = -1
    ws1.Range("A1").Copy
    
    Actual_Rng.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
        
    ws2.Select
    ws2.Range("A1") = -1
    ws2.Range("A1").Copy
    Budget_Rng.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
    
    ws3.Select
    ws3.Range("A1") = -1
    ws3.Range("A1").Copy
    Forecast_Rng.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

 

[Richard Schollar]

Did you mean for all your ranges that you set in the beginning to point at the same sheet?

You can't select a range on a sheet that is not Active, and that is what you are trying to do here:

ws2.Select
    ws2.Range("A1") = -1
    ws2.Range("A1").Copy
    Budget_Rng.Select

ws1 holds the Budget range, not ws2.

 

[karenbluez]

The code must activate sheet 1, select the range, copy and paste with negative 1, then select/activate sheet 2, go through the same procedure, then do the same with sheet 3.

I've tried to activate the sheets, but still get the same error message.


[EDIT] Think I've found the answer in your post, Richard.

I've changed the code as follows and declare the ranges with the relevant worksheets and now it runs every time!

Thank you.

    Set Actual_Rng = Application.Union(ws1.Range("C88:AM101"), ws1.Range("C103:AM114"), ws1.Range("C116:AM131"), ws1.Range("C143:AM156"), ws1.Range("C158:AM169"), ws1.Range("C170:AM186"), ws1.Range("C198:AM211"), ws1.Range("C213:AM224"), ws1.Range("C226:AM241"), ws1.Range("C253:AM266"), ws1.Range("C268:AM279"), ws1.Range("C281:AM296"))
    Set Budget_Rng = Application.Union(ws2.Range("C88:AL101"), ws2.Range("C103:AL114"), ws2.Range("C116:AL131"), ws2.Range("C143:AL156"), ws2.Range("C158:AL169"), ws2.Range("C170:AL186"), ws2.Range("C198:AL211"), ws2.Range("C213:AL224"), ws2.Range("C226:AL241"), ws2.Range("C253:AL266"), ws2.Range("C268:AL279"), ws2.Range("C281:AL296"))
    Set Forecast_Rng = Application.Union(ws3.Range("C88:R101"), ws3.Range("C103:R114"), ws3.Range("C116:R131"), ws3.Range("C143:R156"), ws3.Range("C158:R169"), ws3.Range("C170:R186"), ws3.Range("C198:R211"), ws3.Range("C213:R224"), ws3.Range("C226:R241"), ws3.Range("C253:R266"), ws3.Range("C268:R279"), ws3.Range("C281:R296"))

 

[Richard Schollar]

So are the ranges all on different sheets?

 

[karenbluez]

Yes the data is on multiple sheets. You beat me though with your reply and my edit.
Thank you for your help. Sometimes is these small things that really throw the curve balls.

 

[Richard Schollar]

Try this - no need to Select or Activate the sheets to workwith them:

  Set ws1 = Worksheets("Actual")
Set ws2 = Worksheets("Budget")
Set ws3 = Worksheets("Forecast")
With ws1
   Set Actual_Rng = Application.Union(.Range("C88:AM101"), .Range("C103:AM114"), _
       .Range("C116:AM131"), .Range("C143:AM156"), .Range("C158:AM169"), _
       .Range("C170:AM186"), .Range("C198:AM211"), .Range("C213:AM224"), _
       .Range("C226:AM241"), .Range("C253:AM266"), .Range("C268:AM279"), .Range("C281:AM296"))
End With
With ws2
    Set Budget_Rng = Application.Union(.Range("C88:AL101"), .Range("C103:AL114"), _
        .Range("C116:AL131"), .Range("C143:AL156"), .Range("C158:AL169"), _
        .Range("C170:AL186"), .Range("C198:AL211"), .Range("C213:AL224"), _
        .Range("C226:AL241"), .Range("C253:AL266"), .Range("C268:AL279"), .Range("C281:AL296"))
End With
With ws3
    Set Forecast_Rng = Application.Union(.Range("C88:R101"), .Range("C103:R114"), _
        .Range("C116:R131"), .Range("C143:R156"), .Range("C158:R169"), .Range("C170:R186"), _
        .Range("C198:R211"), .Range("C213:R224"), .Range("C226:R241"), .Range("C253:R266"), _
        .Range("C268:R279"), .Range("C281:R296"))
End With
ws1.Range("A1") = -1
ws1.Range("A1").Copy
Actual_Rng.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
Budget_Rng.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
Forecast_Rng.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
ws.Range("A1").ClearContents

 

[karenbluez]

Works like a charm!!! Thank you!!!