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

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.
" wrote:

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

On Nov 5, 12:54 pm, Joel wrote:
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.



" wrote:
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- Hide quoted text -

- Show quoted text -

Ahhh! Ty :) it works!!!!