Continuing problem populating userform from a previous record

[Dooley007]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[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" <[email protected]>
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

 

[Jim Cone]

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" <[email protected]>
wrote in message

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

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

Dooley007

 

[Jim Cone]

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)

 

[Dooley007]

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]

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" <[email protected]>
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

 

[Dooley007]

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