Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > UserForm-Code Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 430
Default UserForm-Code Problem
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default UserForm-Code Problem
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

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:bdVAd.15466$jn.6848@lakeread06...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default UserForm-Code Problem
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.
--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:bdVAd.15466$jn.6848@lakeread06...
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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 430
Default UserForm-Code Problem
Much appreciated Tom;
Happy New Year to you
and your family.


"Tom Ogilvy" wrote in message
...
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.
--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:bdVAd.15466$jn.6848@lakeread06...
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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform Code scrabtree23[_3_] Excel Programming 3 December 5th 04 05:05 AM
Have userform & reams of code! Mark \(InWales\)[_10_] Excel Programming 3 October 27th 04 07:30 AM
UserForm code help needed. madbloke[_9_] Excel Programming 1 September 23rd 04 10:53 AM
UserForm code help needed. madbloke[_8_] Excel Programming 1 September 22nd 04 07:05 PM
VBA code for Userform Martin Los Excel Programming 4 December 5th 03 03:04 PM


All times are GMT +1. The time now is 08:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement