Formatted to be readable

I have data sheet whose data appears to be like :


Number Name Region
123 Mike London
456 Jake Singapore
789 Steve Japan
741 Steve New York


This is what i need ....


Whenever a user select the number from Listbox via userform, and if
that number already exsists then the macro should search for the that
number in the entire workbook and without entering the number again it
should insert a row with the user name in name column, with their
relavent region in region column, with their comments(text box in
userform) in comments column. If its a new number then it should
insert all the information with number


Number * * * * * * * * * * * * * * * * * * * * * * * * *Name * * * * *
*Region * * * * Users Comments
123 * * * * * * * * * * * * * * * * * * * * * * * * * * Mike * * * * *
*London * * * Comments
456 * * * * * * * * * * * * * * * * * * * * * * * * * * Jake * * * * *
*Singapore * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * *Steve * * * *
* Japan * * * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * Mike * * * * *
*New York * * * Comments
Insert Row here when "456" selected from the listbox in userform and
their relavent name & region in datasheet with user comments
789 * * * * * * * * * * * * * * * * * * * * * * * * * * Steve * * * *
* *ingapore * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * *Mike * * * * *
*Japan * * * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * *Jake * * * * *
*New York * * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * *Steve * * * *
* London * * * * Comments
Empty Cell * * * * * * * * * * * * * * * * * * * * * * *Mike * * * * *
*Singapore * * Comments
Insert Row here when "789" selected from the listbox in userform and
their relavent name & region in datasheet with user comments
741 * * * * * * * * * * * * * * * * * * * * * * * * * * Steve * * * *
* New York * * * Comments
Insert new data here when 147 selected from listbox in userform and
their relavent name & region in datasheet with user comments


On Sep 3, 10:24*am, Hasan wrote:
To be more clear...

I have data sheet whose data appears to be like :

Number *Name * *Region
123 * * Mike * *London
456 * * Jake * *Singapore
789 * * Steve * Japan
741 * * Steve * New York

This is what i need ....

I want the user to select the number from Listbox and the name &
region as it shouldpopulateas they appear in data sheet for the
respective number via userform

Number * * * * * * * * * * * * * * * * * * * * * * * * * *Name * * * * * * Region * * * * * * Users
Comments
123 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Mike * * * * * *London * * * * * * * * *Comments as per
textbox in userform
456 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Jake * * * * * *Singapore * * Comments as per textbox
in userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * * * * * *Steve * * * * * Japan * * * * Comments as per textbox in
userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * * * * * *Mike * * * * * *New York * * *Comments as per textbox in
userformInsertRowhere when "456" selected from the listbox in userform
Jake * * * * * * *London * * * * * * * * *Comments as per textbox in userform
789 * * * * * * * * * * * * * * * * * * * * * * * * * * * Steve * * * * * *Singapore * *Comments as per textbox in
userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * *Mike * * * * * *Japan * * * * Comments as per textbox in userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * *Jake * * * * * *New York * * *Comments as per textbox in userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * *Steve * * * * * London * * * * * * * * *Comments as per textbox in
userform
Empty Cell * * * * * * * * * * * * * * * * * * * * * * * *Mike * * * * * *Singapore * * Comments as per textbox in
userformInsertRowhere when "789" selected from the listbox in userform
Jake * * * * * * *Japan * * * * Comments as per textbox in userform
741 * * * * * * * * * * * * * * * * * * * * * * * * * * * Steve * * * * * New York * * *Comments as per textbox in userformInsertnew data here when 147 selected from listbox in userform
Mike * * * * * * *London * * * * * * * * Comments as per textbox in userform

On Sep 2, 2:27*pm, Patrick Molloy



wrote:
I'm still unclear. If an item exists,insertarowBELOW it and put what into
it?

take a look at this code and see if you can adapt it...

Option Explicit
Sub demo()
* * InsertValue "D", 17
End Sub
Sub InsertValue(text As String, val As String)
* Dim ws As Worksheet
* Dim rw As Long
* For Each ws In Worksheets
* * * rw = findrow(ws, text)
* * * If rw = 0 Then
* * * * rw = ws.Range("A1").End(xlDown).Row
* * * End If
* * * rw = rw + 1
* * * ws.Rows(rw).Insert
* * * ws.Cells(rw, 1) = text
* * * ws.Cells(rw, 2) = val
* * Next
End Sub
Function findrow(ws As Worksheet, item As String)
* On Error Resume Next
* findrow = WorksheetFunction.Match(item, ws.Range("A1:A" & ws.Range *
("A1").End(xlDown).Row), False)
* On Error GoTo 0
End Function

"Hasan" wrote:
Sorry if it was not clear but that helped me reach there almost.
Thanks

I got the list box that populates Sheet1 column A data.

Now i want the code to seach the selected listbox value in Column A of
entire workbook andinsertarowjust above the next data/filled cell

Example :

Column A
123
234
567

---------------- Seach for 567 andinsertrowhere
789

In an above example whenever i select value from Listbox(say 567) it
should search for that value in entire wokbook andinsertarowjust
above the next data/filled cell(say above 789). If the value not found
theninsertthe data in next emptyrow.

On Sep 1, 6:38 pm, Patrick Molloy
wrote:
its not clear what you need
there are many ways to getvaluesinto a userform.
The follwoing code willpopulatea listbox from column A in sheet1

rw = 1
with worksheets("Sheets1")
* *do until .Cells(rw,1)=""
* * * *listbox1.AddItem .cells(rw,1)
* * * rw = rw+1
* loop
End with

put this in a sub on the form's code page, then call the sun from the
userform initialse event

"Hasan" wrote:
Hi,

I am looking for a macro that shouldpopulateSheet1valuesin
userform and check for the samevaluesin cloumn A of sheet2(where the
macro resides). If the value is found in(Sheet2) then look for the
next filled cell in column A andinsertrowabove.

Thanks in advance- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -