Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing problem populating userform from a previous record
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open new record with selected fields from previous record | Excel Discussion (Misc queries) | |||
Populating fields based on previous column values | Excel Programming | |||
Re-Populating Previous Answers into a User Form from the Spreadshe | Excel Programming | |||
problem with populating a combo box on a userform | Excel Programming | |||
Need help autopopulating next new record with previous record data | Excel Programming |