CODEWITHSUNDEEP
vbaexcelfind
Romita Thakur
Romita Thakur

Reputation: 333

Variable not set and error 91 using in VBA

Hi I am facing an issue with VBA for variable not set.

Dim rngvar1 As Range
Set rngvar1 = ActiveWorksheet.Range("K" + CStr(strng.Row) + ":K" + _
    CStr(endrng.Row)).Find(what:=maxdate, LookIn:=xlValues)

I am getting valuea in strng.Row, endrng.Row and in maxdate also but getting 'nothing' in rngvar1.

Also getting -4163 in LookIn:=xlValues.

Upvotes: 0

Views: 39

Answers (2)

Vityata
Vityata

Reputation: 43595

If rngvar1 is Nothing, then you cannot use .Find for it. Thus, make a check whether it is "Not Nothing", before assigning:

Dim rngvar1 As Range
Set rngvar1 = ActiveWorksheet.Range("K" + CStr(strng.Row) + ":K" + CStr(endrng.Row))
If Not rngvar1 Is Nothing Then
    Set rngvar1 = rngvar1.Find(what:=maxDate, LookIn:=xlValues)
End If

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

A couple of things in your code:

  1. Have Error handling scenario if Find failed to fetch maxdate.
  2. You are searching for a Date, try using LookIn:=xlFormulas.

The rest of my comments in the code below:

Dim Sht As Worksheet
Dim rngvar1 As Range

Set Sht = ActiveSheet ' <-- rather not use ActiveSheet, better use Worksheets("YourSheetName")

With Sht
    Set rngvar1 = .Range(.Cells(strng.Row, "K"), .Cells(endrng.Row, "K")).Find(what:=maxdate, LookIn:=xlFormulas)

    If Not rngvar1 Is Nothing Then ' If Find was successfull
        ' put the rest of your code here

    Else ' Find failed >> raise an error message
        MsgBox "Error finding " & maxdate, vbCritical
    End If
End With

Upvotes: 1

Related Questions