CODEWITHSUNDEEP
vbaexcel
jadeliew123
jadeliew123

Reputation: 17

Variable range and keep getting "method range of object _worksheet failed"

I'm new to VBA and I know similar questions have been asked but I cant seem to find the answer. I just want to have a variable range to allow the macro to go through all values one by one down the list, but it keeps giving me the same error. also, i want to compare all the numbers in a certain range and pick the first value that is more than another value (all in the same row). i dont know if i'm doing it right, the code is here: 

Option Explicit

Private Sub CommandButton1_Click()

Dim buysignal As Variant
Dim OHLC As Variant
Dim ffdhigh As Variant
Dim i As Long

Sheets("sheet2").Activate
Set buysignal = Range("M" & i).Value
OHLC = Range("B" & i & ":" & "E" & i).Value
ffdhigh = Range("I" & i).Value

For i = 63 To 150
If OHLC > ffdhigh Then
buysignal = "buy"
Else: buysignal = ""

End If
Next i

End Sub

error message : Run-time error '1004': Method 'Range' of object '_worksheet' failed

Thanks guys, any input will be very appreciated.

Upvotes: 0

Views: 280

Answers (1)

teylyn
teylyn

Reputation: 35990

Your variable i does not have a value yet, and you are using it to set the buysignal range. So, set i to a value.

Next, the same line will throw an error because you use set to set the value of an object, but you set it to the value of a cell. That's a type mismatch.

Plan what you want to do before you start coding. Oh, and don't use variants for everything. It is asking for the kind of trouble you're facing now. Properly type your variables, then you will catch type mismatches right away.

Upvotes: 2

Related Questions