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" <(E-Mail Removed)>
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" <(E-Mail Removed)>
> 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" <(E-Mail Removed)>
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

In the cbSearch sub, you have to have the line...
UserForm1.TextBox1.Tag = CStr(y)
and the y value cannot be zero.
Also, check the UserForm name you are using.
Is it UserForm1, UserForm4 or something else?
Jim Cone


"Dooley007" <(E-Mail Removed)>
wrote in message
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

Jim Cone wrote:
> In the cbSearch sub, you have to have the line...
> UserForm1.TextBox1.Tag = CStr(y)
> and the y value cannot be zero.
> Also, check the UserForm name you are using.
> Is it UserForm1, UserForm4 or something else?
> Jim Cone


Jim;

Thanks for your reply. It stll kept coming up with an error so I
played round with the code
and took out the two lines that you added. Then I reset the line "Set
Lastrow = cells(y,1)"
to "Set Lastrow = Range(st)" and the program works beautifully!

I very much appreciate your assistance (you got me thinking "outside
the box ...so to speak" and I tried the code line that Imentioned.

I would like to take this opportunity to wish you a very Merry
Christmas and a Happy New Year.

Dooley007