Userform to search one after the other record and send to new worksheet

[DawidV]

I am new in Excel VBA and I am trying to make a user-form that must do the following.

We are using a excel spreadsheet that gets updated monthly. from that name list (PICKLIST) we then on a regular basis search by number and then we make other name lists with the data that is linked to the force number that we have searched for.

I have made the user form (as attch file) and all is working. I got the code sorted to search for the first record and it populates the user form. when I use a number other than the first number in the ws then it does not populate the user form.

I got it to send that data to the bottom of the PICKLIST but that is not what i want. I want the information to be send to a ws named DATA. There is ±480 records on the PICKLIST ws.

Included is the code as I have it at present.

Private Sub cmdClose_Click()

'WHEN CLOSE BUTTEN IS CLICKED THE USERFORM CLOSES WITH EXELL SPREADSHEET VISABLE

Unload Me

End Sub

Private Sub cmdSearch_Click()

'SEARCH THE ACTIVE WORKSHEET WITH FORCE NO TO POPULATE THE DATA SEARCH FORM

txtForceNo.Text = Range("B8").Value
txtAC.Text = Range("C8").Value
txtRank.Text = Range("D8").Value
txtInI.Text = Range("E8").Value
txtSurname.Text = Range("F8").Value
txtCorps.Text = Range("G8").Value
txtRSAID.Text = Range("H8").Value
txtRace.Text = Range("I8").Value
txtGender.Text = Range("J8").Value
txtFormerForce.Text = Range("K8").Value
txtRemarks.Text = Range("L8").Value

End Sub

Private Sub cmdSendTo_Click()

'DATA THAT WAS POPULATED IN THE DATA SEARCH FORM WILL NOW BE SEND TO THE DATA SHEET

Range("B8").Select

'FIND OUT HOW MANY ROWS ON THE SPREADSHEET HAS BEEN USED

ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
'MsgBox lastrow GET THE NUMBER OF THE LAST ROW USED LAST ROW TO BE SHOWN ON THE MESSAGE BOX
Cells(lastrow + 1, 2).Value = txtForceNo.Text
Cells(lastrow + 1, 3).Value = txtAC.Text
Cells(lastrow + 1, 4).Value = txtRank.Text
Cells(lastrow + 1, 5).Value = txtInI.Text
Cells(lastrow + 1, 6).Value = txtSurname.Text
Cells(lastrow + 1, 7).Value = txtCorps.Text
Cells(lastrow + 1, 8).Value = txtRSAID.Text
Cells(lastrow + 1, 9).Value = txtRace.Text
Cells(lastrow + 1, 10).Value = txtGender.Text
Cells(lastrow + 1, 11).Value = txtFormerForce.Text
Cells(lastrow + 1, 12).Value = txtRemarks.Text

'CLEAR ALL FIELDS OF DATA FORM ONCE DATA HAS BEEN TRANSFERRED

Range("B8").Select

txtForceNo.Text = ""
txtAC.Text = "' "
txtRank.Text = ""
txtInI.Text = ""
txtSurname.Text = ""
txtCorps.Text = ""
txtRSAID.Text = ""
txtRace.Text = ""
txtGender.Text = ""
txtFormerForce.Text = ""
txtRemarks.Text = ""

End Sub



Private Sub UserForm_Click()

End Sub

PLEASE I NEED HELP!!!!!!!!

Greetings

Dawid








__________________________________________________________________

Mod edit : thread moved to appropriate forum !
​

 

[Deepak]

Plz upload the sample of the said workbook.

 

[DawidV]

I am new in Excel VBA and I am trying to make a user-form that must do the following.

We are using a excel spreadsheet that gets updated monthly. from that name list (PICKLIST) we then on a regular basis search by number and then we make other name lists with the data that is linked to the force number that we have searched for.

I have made the user form (as attch file) and all is working. I got the code sorted to search for the first record and it populates the user form. when I use a number other than the first number in the ws then it does not populate the user form.

I got it to send that data to the bottom of the PICKLIST but that is not what i want. I want the information to be send to a ws named DATA. There is ±480 records on the PICKLIST ws.View attachment 32131 View attachment 32132 View attachment 32133

Included is the code as I have it at present.

Private Sub cmdClose_Click()

'WHEN CLOSE BUTTEN IS CLICKED THE USERFORM CLOSES WITH EXELL SPREADSHEET VISABLE

Unload Me

End Sub

Private Sub cmdSearch_Click()

'SEARCH THE ACTIVE WORKSHEET WITH FORCE NO TO POPULATE THE DATA SEARCH FORM

txtForceNo.Text = Range("B8").Value
txtAC.Text = Range("C8").Value
txtRank.Text = Range("D8").Value
txtInI.Text = Range("E8").Value
txtSurname.Text = Range("F8").Value
txtCorps.Text = Range("G8").Value
txtRSAID.Text = Range("H8").Value
txtRace.Text = Range("I8").Value
txtGender.Text = Range("J8").Value
txtFormerForce.Text = Range("K8").Value
txtRemarks.Text = Range("L8").Value

End Sub

Private Sub cmdSendTo_Click()

'DATA THAT WAS POPULATED IN THE DATA SEARCH FORM WILL NOW BE SEND TO THE DATA SHEET

Range("B8").Select

'FIND OUT HOW MANY ROWS ON THE SPREADSHEET HAS BEEN USED

ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
'MsgBox lastrow GET THE NUMBER OF THE LAST ROW USED LAST ROW TO BE SHOWN ON THE MESSAGE BOX
Cells(lastrow + 1, 2).Value = txtForceNo.Text
Cells(lastrow + 1, 3).Value = txtAC.Text
Cells(lastrow + 1, 4).Value = txtRank.Text
Cells(lastrow + 1, 5).Value = txtInI.Text
Cells(lastrow + 1, 6).Value = txtSurname.Text
Cells(lastrow + 1, 7).Value = txtCorps.Text
Cells(lastrow + 1, 8).Value = txtRSAID.Text
Cells(lastrow + 1, 9).Value = txtRace.Text
Cells(lastrow + 1, 10).Value = txtGender.Text
Cells(lastrow + 1, 11).Value = txtFormerForce.Text
Cells(lastrow + 1, 12).Value = txtRemarks.Text

'CLEAR ALL FIELDS OF DATA FORM ONCE DATA HAS BEEN TRANSFERRED

Range("B8").Select

txtForceNo.Text = ""
txtAC.Text = "' "
txtRank.Text = ""
txtInI.Text = ""
txtSurname.Text = ""
txtCorps.Text = ""
txtRSAID.Text = ""
txtRace.Text = ""
txtGender.Text = ""
txtFormerForce.Text = ""
txtRemarks.Text = ""

End Sub



Private Sub UserForm_Click()

End Sub

PLEASE I NEED HELP!!!!!!!!

Greetings

Dawid


View attachment 32132



View attachment 32133

__________________________________________________________________

Mod edit : thread moved to appropriate forum !
​

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[DawidV]

Hi:

Find the attached.

Thanks

thankyou very much for the help. the form is now searching for any number as I wanted it to do.

However it still places the information after the last row on the PICKLIST ws and not on the DATA ws. For now I can work with this. it helps me a lot.

On the form is a Field "DOB" that if the data is send to the last row it does not stay in the same format as it is in the ws. how do i rectify that. I have placed two names at the end of the PICKLIST ws to explain this.

agin thanyiou very much. I appreciate it.
Regards

Dawid

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[DawidV]

Hi


Thank you for the help with my form. I really appreciated it. It is working well.

Could you please help me with a few refinements.


We search by number all the time. Column “Force Number” on the spreadsheet. The number MUST ALWAYS have 08 digits. Some of the numbers is not 08 digits long and gets leading 000 (from 1 to 4) in front to make the number 08 digits.


I tried to set the number format in the Format Cells/Custom but this does not seem to work. Currently if you search with a number that has a leading 0 you get the error message which is correct. If possible the search must also work if there is a leading 0.


SEND TO WORKSHEET


First row to use on DATA sheet must be .Cells(8, 2).Value = txtForceNo.Text The selections must be placed one below each other on the DATA ws. We sometimes use lists of up to 30 members. I have made the DATA WS AS IT SHOULD BE. Must be placed below each other on the DATA ws.


Once the selection was send to the worksheet clear the form and the cursor must go back to the FORCE NO field.

Thanx

Dawid

 

[Nebu]

Hi:

While searching do not put the leading 0's. The find function is treating your search parameters as numeric hence you are getting an error. If you convert all your number into text the leading zeros will not give you an error, but my suggestion is that there is no need of doing that, simply ignore the leading zeros while you enter the search parameter the macro will work just fine.

Thanks