UserForm-Code Problem

[Jim May]

Stepping thru the Below code goes OK EXCEPT after running
Set rngordDate = .Find(SearchOrderDate, LookIn:=xlFormulas) ' 8 lines from
bottom
rngordDate = Nothing <<< so it's no wonder I get R/T error 91 on next
line;
Can someone assist me?
TIA,

Private Sub CommandButton1_Click()
Dim dtOrder As Range
Dim intOrder As Range
Dim billAmt As Double
Dim ws As Worksheet
Dim searchOrder
Dim rngorder As Range
Dim rngordDate As Range
Set ws = Sheets("Sheet2")
Set dtOrder = ws.Range("C4:I4")
Set intOrder = ws.Range("B5:B14")
searchOrder = Me.TextBox1.Value
billAmt = Me.TextBox3.Value
With intOrder
Set rngorder = .Find(searchOrder)
If Not rngorder Is Nothing Then

SearchOrderDate = Me.TextBox2.Value
SearchOrderDate = DateValue(SearchOrderDate)
With dtOrder
Set rngordDate = .Find(SearchOrderDate, LookIn:=xlFormulas)
rngordDate.Offset(rngorder.Row - 4, 0).Value = billAmt
End With
Else
MsgBox "Order not found, Try Again", vbInformation
End If
End With
End Sub

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim dtOrder As Range
Dim intOrder As Range
Dim billAmt As Double
Dim ws As Worksheet
Dim searchOrder
Dim rngorder As Range
Dim rngordDate As Range
Set ws = Sheets("Sheet2")
Set dtOrder = ws.Range("C4:I4")
Set intOrder = ws.Range("B5:B14")
searchOrder = Me.TextBox1.Value
billAmt = Me.TextBox3.Value
With intOrder
Set rngorder = .Find(searchOrder)
If Not rngorder Is Nothing Then

SearchOrderDate = Me.TextBox2.Value
SearchOrderDate = DateValue(SearchOrderDate)
With dtOrder
Set rngordDate = .Find(SearchOrderDate, LookIn:=xlFormulas)
if rngordDate is nothing then
msgbox "Not found"
else
rngordDate.Offset(rngorder.Row - 4, 0).Value = billAmt
end if
End With
Else
MsgBox "Order not found, Try Again", vbInformation
End If
End With
End Sub

 

[Tom Ogilvy]

If you actually want to find the date if it exists, you might use
application.match instead:

Private Sub CommandButton1_Click()
Dim dtOrder As Range
Dim intOrder As Range
Dim billAmt As Double
Dim ws As Worksheet
Dim searchOrder
Dim rngorder As Range
Dim rngordDate As Range
Dim res as Variant
Set ws = Sheets("Sheet2")
Set dtOrder = ws.Range("C4:I4")
Set intOrder = ws.Range("B5:B14")
searchOrder = Me.TextBox1.Value
billAmt = Me.TextBox3.Value
With intOrder
Set rngorder = .Find(searchOrder)
End with
If Not rngorder Is Nothing Then

SearchOrderDate = Me.TextBox2.Value
SearchOrderDate = DateValue(SearchOrderDate)
res = Application.Mactch(clng(SearchOrderDate),dtOrder,0)
if iserror(res) then
msgbox "Not found"
else
Set rngordDate =DtOrder(1,res)
rngordDate.Offset(rngorder.Row - 4, 0).Value = billAmt
End if

Else
MsgBox "Order not found, Try Again", vbInformation
End If
End With
End Sub

Find doesn't do well with dates in my experience.

 

[Jim May]

Much appreciated Tom;
Happy New Year to you
and your family.