Reputation: 333
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
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
Reputation: 33692
A couple of things in your code:
Find
failed to fetch maxdate
.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