CODEWITHSUNDEEP
excelvba
Shashank Naik
Shashank Naik

Reputation: 7

run time error 91 vba

I had to get values of crest and trough of a sin wave. But the data has noise. I used the following code. But it seems to be having problem in one part of the code. It gives run time error 91.

Here is the code.

Range("B2").Activate
last = Application.CountA(Range("A:A"))
Dim f, g, tp, tm As Double
Dim zpo, zpt, zmo, zmt As Range
f = 0
g = 0
For a = 0 To 2
For k = 0 To last
    If ActiveCell.Offset(k, a).Value > 0 Then
        If ActiveCell.Offset(k + 1, a).Value < 0 Then
            zpo = ActiveCell.Offset(k, a).Address
            zmo = ActiveCell.Offset(k + 1, a).Address
            tp = f + 1
            f = tp
        End If
    End If
    If ActiveCell.Offset(k, a).Value < 0 Then
        If ActiveCell.Offset(k + 1, a).Value > 0 Then
            zmt = ActiveCell.Offset(k, a).Address
            zpt = ActiveCell.Offset(k + 1, a).Address
            tm = g + 1
            g = tm
        End If
    End If
    If f > 0 Then
        If f = g Then
            Sheets("extract").Cells((5 * a) + 1, f).Value = WorksheetFunction.max("zpo:zpt")
            Sheets("extract").Cells((5 * a) + 2, f).Value = WorksheetFunction.Min("zmo:zmt")
        End If
     End If
Next
Next

It has problems when it reaches the second if statement.

It says object variable or block variable not set

Upvotes: 0

Views: 153

Answers (1)

El Scripto
El Scripto

Reputation: 576

You got an error because your code tried to put a string into an object:

  Dim...zmt As Range  (<--- zmt is set as an object becuase Range is an object)

  zpt = ActiveCell.Offset(k + 1, a).Address   (the "Address" is a string)

Generally speaking, when you have an object, you need to use "set", for example:

  set object1 = object2

This is not the case with your code, because a "string" is not considered to be an object in VBA, so you so not use "set" when dealing with them.

The main issue is that, when you define a line:

    Dim zpo, zpt, zmo, zmt As Range

what you are actually doing is asking for: "zpo, zpt, zmo"
to be variables of Variant type and zmt to be a Range type (an object).

Once you changed zmt to be a variant, the code worked, as you saw yourself, becuase a string (Address) can feet into a Variant.

Upvotes: 1

Related Questions