VBA: Application-defined error when refering to range in different tab

Question

In this sub, I'm simply doing a few calculations. In a differnt sheet I've some temporary data stored and I'm trying and failing to define the following as a range (at the very bottom at the code):

MeanVector = Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))

I get the following error: 1004 application-defined or object-defined error

The thing though is, this is a line of code that I copied in from a different module, so I know the method itself works.

I assume the problem is with the activation of the sheets, I'm tried to activate both at the beginning but with no luck. I do specify the worksheet in evry line of code, so I'm not sure why it would still be a problem?

I've tried to select the range as well:

Set MeanVector = Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))

All code:

Sub CalculateOwnPortfolio()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Calculating..."

Dim MeanVector As Range
Dim WeightsVector As Range


Worksheets("TempSheet").Activate
Worksheets("Own Portfolio").Activate

'Count the amount of stock
Stocks = 0
For i = 1 To 20
    If Worksheets("MainSheet").Cells(i + 2, 2) <> 0 Then
        Stocks = Stocks + 1
    Else
        Stocks = Stocks
    End If
Next

'Amount of each stock
EmptyAmount = 0
For i = 1 To Stocks
    If Worksheets("Own Portfolio").Cells(1 + i, 7) = Value Then
        Else
            EmptyAmount = EmptyAmount + 1
    End If
Next
If EmptyAmount = 0 Then
    MsgBox ("Error: Enter stock amounts")
    Exit Sub
End If

'Calcualte amount of observations
Observations = 0
For j = 2 To 15000
    If Worksheets("Own Portfolio").Cells(j, 1) <> 0 Then
        Observations = Observations + 1
    Else
        Observations = Observations
    End If
Next

Worksheets("Own Portfolio").Range(Cells(2, 2), Cells(Observations, 2)) _
    .ClearContents
Worksheets("Own Portfolio").Range(Cells(2, 5), Cells(3 + Stocks, 5)) _
    .ClearContents


'Total Value
For i = 2 To Observations + 1
    Value = 0
    For j = 1 To Stocks
        Symbol = Worksheets("Own Portfolio").Cells(1 + j, 4)
        Amount = Worksheets("Own Portfolio").Cells(1 + j, 7)
        AdjClose = Worksheets(Symbol).Cells(i, 7)
        Value = Value + (Amount * AdjClose)
        Worksheets("Own Portfolio").Cells(i, 2) = Value
    Next
Next

'Weights
TotalValue = 0
For j = 1 To Stocks
    Symbol = Worksheets("Own Portfolio").Cells(1 + j, 4)
    Amount = Worksheets("Own Portfolio").Cells(1 + j, 7)
    AdjClose = Worksheets(Symbol).Cells(2, 7)
    TotalValue = TotalValue + (Amount * AdjClose)
Next

For j = 1 To Stocks
    StockValue = 0
    Symbol = Worksheets("Own Portfolio").Cells(1 + j, 4)
    Amount = Worksheets("Own Portfolio").Cells(1 + j, 7)
    AdjClose = Worksheets(Symbol).Cells(2, 7)
    StockValue = Amount * AdjClose
    Worksheets("Own Portfolio").Cells(1 + j, 5) = StockValue / TotalValue
Next


'Mean,Variance,Std Dev and Sharp Ratio
'-------------------------------------------------------------------------
'----------------This is where I get the error message--------------------
MeanVector = Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))
'-----------------------------------------------------------------------

WeightsVector = Worksheets("Own Portfolio").Range(Cells(2, 5), Cells(Stocks + 1, 2))
Mean = Application.WorksheetFunction.SumProduct(MeanVector, WeightsVector)

Call OwnPortfolioGraph(Symbol)


Application.ScreenUpdating = True
Application.DisplayAlerts = True

Application.StatusBar = False

End Sub

Picture of "TempSheet"

ClintB · Answer 1 · 2017-05-15T15:08:13.800

1

change this:

MeanVector = Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))

to this:

set MeanVector = Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))

and see if it works

then do the same for

WeightsVector = Worksheets("Own Portfolio").Range(Cells(2, 5), Cells(Stocks + 1, 2))

Edit: I'm pretty sure that in your formula Worksheets("TempSheet").Range(Cells(2, 2), Cells(Stocks + 1, 2))

Unless the current worksheet is tempsheet then Cells(2, 2) will not be the value your expecting, neither will Cells(Stocks + 1, 2). you could try:

With Worksheets("TempSheet")
    WeightsVector = .Range(.Cells(2, 5), .Cells(Stocks + 1, 2))
end with

although looking at the screenshot you have added it doesnt appear cells(2,5) is from tempsheet

edited May 15 '17 at 15:08

answered May 15 '17 at 14:51

ClintB

509
3
6

No, still the same problem. The WeightsVector does work though, even without "Set". That's why I would assume the problem lies with the worksheet itself and selecting it. – Dennis Christiansen May 15 '17 at 14:58
What's in the current sheet Cells(2, 2) and Cells(Stocks + 1, 2) – ClintB May 15 '17 at 15:00
I edited my original post and added a picture of TempSheet. Stocks = 3 in this case. – Dennis Christiansen May 15 '17 at 15:03
I think you misunderstood the last part, the Weights of the different stocks are in a different spreadsheet. I already created the means (in the temp sheet) in a different module so I'm simply trying to use that instead of creating it again. It still dont work though, I tried read the answer to the "doublicate" problem, but without luck. – Dennis Christiansen May 17 '17 at 17:52
Different workbook or different sheet within the same workbook? – ClintB May 18 '17 at 01:07
The two sheets are in the same workbook – Dennis Christiansen May 21 '17 at 15:16

VBA: Application-defined error when refering to range in different tab

1 Answers1