I have written a programme to find a previous record in my database
(presently on 35 records but will eventually grow to about 1500
records).

This is my search program (It finds the record that I want based on my
inputing the Last Name of the person whose record I want) and it works
flawlessly each tim.

Option Explicit
Sub cbSearch()

Dim Searchvar As String
Dim i As Integer
Dim x, y As String
Dim st As String

'Sheets(1).Activate

Searchvar = InputBox("Enter the Lastname to find")
Searchvar = Trim$(Searchvar) ' removes surplus spaces


For i = 5 To 1500
x = Cells(i, 1).Value
y = Cells(i, 1).row

If Cells(i, 1).Value = Searchvar Then
MsgBox ("Found it! Its ") & Cells(i, 1).Value & (" at row
") & Str(y)
Cells(y, 1).Activate
st = Selection.Address
MsgBox st
UserForm4.Show

' GoTo Recfixed
End If

If Cells(i, 1).Value = "" Then
End If
Next
Recfixed:
End Sub


Once the program goes to "Userform4.show" I want to be able to pull
information from the record and plucg it back into the userform4.

Here is the code for the Userform4.

Private Sub UserForm_Initialize()
Dim Ts, Lu, y, i As Integer
Dim x As String
Dim st As String
Dim lastrow As Range
ActiveWorkbook.Sheets(1).Activate

st = Selection.Address
With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With

' Set lastrow = Range(x)
' y = Cells(i, 1).row
' textbox.Text =
Application.Selection.Cells[Application.ActiveCell.Row,
'TextBoxTagNumber].Value




Textbox1.Text = lastrow.Offset(, 9).Value
Textbox2.Text = lastrow.Offset(, 10).Value
Textbox3.Text = lastrow.Offset(, 11).Value
Textbox4.Text = lastrow.Offset(, 12).Value
Textbox5.Text = lastrow.Offset(, 13).Value
Textbox6.Text = lastrow.Offset(, 14).Value

End Sub

Theprogram generates an error message
"Run time error '1004'
Application - defined or object-defined error"

at the point "Set lastrow = Cells(y, 1)".

I have tried several different fixes but an error still occors.

Anyone got any ideas? This is driving me nuts.

Dooley007

You have to tell Excel what a range is referring to before you use it.
The With statement has to come after the Set statement.
The "With lastrow" and "End With" should be removed...
'----------------------
With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With
'----------------------
Looks like your spell checker isn't working either.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dooley007"
wrote in message
I have written a programme to find a previous record in my database
(presently on 35 records but will eventually grow to about 1500
records).
-snip-
Once the program goes to "Userform4.show" I want to be able to pull
information from the record and plucg it back into the userform4.
Here is the code for the Userform4.

Private Sub UserForm_Initialize()
Dim Ts, Lu, y, i As Integer
Dim x As String
Dim st As String
Dim lastrow As Range
ActiveWorkbook.Sheets(1).Activate
st = Selection.Address

With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With

' Set lastrow = Range(x)
' y = Cells(i, 1).row
' textbox.Text =
-snip-
End Sub

Theprogram generates an error message
"Run time error '1004'
Application - defined or object-defined error"
at the point "Set lastrow = Cells(y, 1)".
I have tried several different fixes but an error still occors.
Anyone got any ideas? This is driving me nuts.
Dooley007

Jim Cone wrote:
You have to tell Excel what a range is referring to before you use it.
The With statement has to come after the Set statement.
The "With lastrow" and "End With" should be removed...
'----------------------
With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With
'----------------------
Looks like your spell checker isn't working either.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dooley007"
wrote in message
I have written a programme to find a previous record in my database
(presently on 35 records but will eventually grow to about 1500
records).
-snip-
Once the program goes to "Userform4.show" I want to be able to pull
information from the record and plucg it back into the userform4.
Here is the code for the Userform4.

Private Sub UserForm_Initialize()
Dim Ts, Lu, y, i As Integer
Dim x As String
Dim st As String
Dim lastrow As Range
ActiveWorkbook.Sheets(1).Activate
st = Selection.Address

With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With

' Set lastrow = Range(x)
' y = Cells(i, 1).row
' textbox.Text =
-snip-
End Sub

Theprogram generates an error message
"Run time error '1004'
Application - defined or object-defined error"
at the point "Set lastrow = Cells(y, 1)".
I have tried several different fixes but an error still occors.
Anyone got any ideas? This is driving me nuts.
Dooley007


Jim

Thanks for the quick reply. It still comes up with the same error in
the same line.

Dooley007

y does not have a value in ...
Set lastrow = Cells(y, 1)
--
Jim Cone
San Francisco, USA

(top posting is preferred)
http://www.officeletter.com/blink/specialsort.html



"Dooley007"
wrote in message
Jim Cone wrote:
You have to tell Excel what a range is referring to before you use it.
The With statement has to come after the Set statement.
The "With lastrow" and "End With" should be removed...
'----------------------
With lastrow
Set lastrow = Cells(y, 1)
' .End(xlToLeft)
End With
'----------------------
Looks like your spell checker isn't working either.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Jim

Thanks for the quick reply. It still comes up with the same error in
the same line.

Dooley007

This ought to work, but I haven't tested it.
Note the added line.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub cbSearch()
Dim Searchvar As String
Dim i As Long
Dim y As Long
Dim x As String
Dim st As String

Searchvar = InputBox("Enter the Lastname to find")
Searchvar = Trim$(Searchvar) ' removes surplus spaces

For i = 5 To 1500
x = Cells(i, 1).Value
y = Cells(i, 1).Row

If Cells(i, 1).Value = Searchvar Then
MsgBox ("Found it! Its ") & Cells(i, 1).Value & _
(" at row") & Str(y)
Cells(y, 1).Activate
st = Selection.Address
MsgBox st
UserForm1.TextBox1.Tag = CStr(y) '<<< Line added
UserForm1.Show

' GoTo Recfixed
End If

If Cells(i, 1).Value = "" Then
End If
Next
Recfixed:
End Sub
'---------

Private Sub UserForm_Initialize()
Dim y As Long
Dim i As Long
Dim x As String
Dim st As String
Dim lastrow As Range
ActiveWorkbook.Sheets(1).Activate

st = Selection.Address
y = CLng(Me.TextBox1.Tag) '<<<
Set lastrow = Cells(y, 1)

TextBox1.Text = lastrow.Offset(, 9).Value
Textbox2.Text = lastrow.Offset(, 10).Value
Textbox3.Text = lastrow.Offset(, 11).Value
Textbox4.Text = lastrow.Offset(, 12).Value
Textbox5.Text = lastrow.Offset(, 13).Value
Textbox6.Text = lastrow.Offset(, 14).Value

End Sub
'------------


"Jim Cone"
wrote in message
y does not have a value in ...
Set lastrow = Cells(y, 1)
--
Jim Cone
San Francisco, USA
(top posting is preferred)
http://www.officeletter.com/blink/specialsort.html

Jim;

it stops at
y = CLng(Me.TextBox1.Tag) '<<< in the "Private Sub
UserForm_Initialize()" program.

and comes upe with a type mismatch error.

Dooley007

Jim Cone wrote:
This ought to work, but I haven't tested it.
Note the added line.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub cbSearch()
Dim Searchvar As String
Dim i As Long
Dim y As Long
Dim x As String
Dim st As String

Searchvar = InputBox("Enter the Lastname to find")
Searchvar = Trim$(Searchvar) ' removes surplus spaces

For i = 5 To 1500
x = Cells(i, 1).Value
y = Cells(i, 1).Row

If Cells(i, 1).Value = Searchvar Then
MsgBox ("Found it! Its ") & Cells(i, 1).Value & _
(" at row") & Str(y)
Cells(y, 1).Activate
st = Selection.Address
MsgBox st
UserForm1.TextBox1.Tag = CStr(y) '<<< Line added
UserForm1.Show

' GoTo Recfixed
End If

If Cells(i, 1).Value = "" Then
End If
Next
Recfixed:
End Sub
'---------

Private Sub UserForm_Initialize()
Dim y As Long
Dim i As Long
Dim x As String
Dim st As String
Dim lastrow As Range
ActiveWorkbook.Sheets(1).Activate

st = Selection.Address
y = CLng(Me.TextBox1.Tag) '<<<
Set lastrow = Cells(y, 1)

TextBox1.Text = lastrow.Offset(, 9).Value
Textbox2.Text = lastrow.Offset(, 10).Value
Textbox3.Text = lastrow.Offset(, 11).Value
Textbox4.Text = lastrow.Offset(, 12).Value
Textbox5.Text = lastrow.Offset(, 13).Value
Textbox6.Text = lastrow.Offset(, 14).Value

End Sub
'------------


"Jim Cone"
wrote in message
y does not have a value in ...
Set lastrow = Cells(y, 1)
--
Jim Cone
San Francisco, USA
(top posting is preferred)
http://www.officeletter.com/blink/specialsort.html