Error 91 Object Variable not set

G

gwoodby

I have 3 Sheets im working with on this project, Sheet 1 Is Shelter,
sheet 2 is Dependency, and Sheet 3 is TPR, however. Sheet 1 and 2
work, but when you run the macro it has error 91 object variable not
set. There is a TxtRatRecd3 Box on The FormLoad UserForm, that is the
correct name, any idea what could be causing it?

Here is Where i Get My Error,

Private Function TPRLoad(StrName As String)
FormLoad.TxtCaseName3 = StrName
FormLoad.LblName = " TPR Sheet "
FormLoad.MultiPage1.Value = 2
Debug.Print FormLoad.TxtRatRecd3
MsgBox StrName
FormLoad.TxtRatRecd3 =
Range("A6:AX4500").Find(FormLoad.TxtCaseName3,
LookIn:=xlValues).Offset(0, 1)

STARTING with the Above Line Highlighted



If FormLoad.TxtPubBegan = "" Then
FormLoad.TxtPubBegan.Enabled = True
Else
FormLoad.TxtPubBegan.Enabled = False
End If

If FormLoad.TxtPubEnded = "" Then
FormLoad.TxtPubEnded.Enabled = True
Else
FormLoad.TxtPubEnded.Enabled = False
End If

LoadMe (StrName)
End Function


Here is the Code for The buttons Prior to these CmdSelect is thefor
the button you press, Function Mysearch Is Searches and should set the
activesheet to the sheet with the name you selected. From there The
Above Code should load the information from excel to the userform
Please help :)
Private Sub CmdSelect_Click()
Dim IntSearch As Integer
IntSearch = MySearch(Me.LboxSelect.Text) ' Call Function to Search
If IntSearch = 1 Then
Found (Me.LboxSelect.Text)
ElseIf IntSearch = 0 Then
MsgBox " No Name Found", vbOKOnly
End If
Exit Sub
ErrorHandler:
MsgBox " Error"
End Sub



Function MySearch(StrToSearch As String)
Dim sh As Worksheet
Dim SearchTxt As String
Dim rng As Range
Dim firstAddress As String
Dim IntNumber As Integer
IntNumber = 0
SearchTxt = StrToSearch

For Each sh In ThisWorkbook.Worksheets
sh.Activate
Set rng = sh.Cells.Find(What:=SearchTxt,
After:=sh.Cells.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=True)
If Not rng Is Nothing Then
MySearch = IntNumber + 1
Exit For
End If
Next sh

End Function
 
J

Joel

When you use a FIND you should always set the results to a variable inbcase
it finds nothing you can check check for this condition

from
FormLoad.TxtRatRecd3 =
Range("A6:AX4500").Find(FormLoad.TxtCaseName3,
LookIn:=xlValues).Offset(0, 1)
to
set c =
Range("A6:AX4500").Find(FormLoad.TxtCaseName3,
LookIn:=xlValues)

if not c is nothing then
FormLoad.TxtRatRecd3 = c.offset(0,1)
end if

Also check if c.offset(0,1) contain some data.
 
G

gwoodby

When you use a FIND you should always set the results to a variable inbcase
it finds nothing you can check check for this condition

from
FormLoad.TxtRatRecd3 =
Range("A6:AX4500").Find(FormLoad.TxtCaseName3,
LookIn:=xlValues).Offset(0, 1)
to
set c =
Range("A6:AX4500").Find(FormLoad.TxtCaseName3,
LookIn:=xlValues)

if not c is nothing then
FormLoad.TxtRatRecd3 = c.offset(0,1)
end if

Also check if c.offset(0,1) contain some data.















- Show quoted text -

Ahhh! Ty :) it works!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top