Populate values and insert row

[Hasan]

Hi,

I am looking for a macro that should populate Sheet1 values in
userform and check for the same values in 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 and insert row above.

Thanks in advance

 

[Patrick Molloy]

its not clear what you need
there are many ways to get values into a userform.
The follwoing code will populate a 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]

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 and insert a row just above the next data/filled cell

Example :

Column A
123
234
567


----------------> Seach for 567 and insert row here
789

In an above example whenever i select value from Listbox(say 567) it
should search for that value in entire wokbook and insert a row just
above the next data/filled cell(say above 789). If the value not found
then insert the data in next empty row.

 

[Patrick Molloy]

I'm still unclear. If an item exists, insert a row BELOW 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)

 

[Hasan]

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

 

[Hasan]

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