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 should populate as 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
userform
Insert Row here 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
userform
Insert Row here 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 userform
Insert new 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 -