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

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" wrote:

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

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.

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 -

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)
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 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.

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 -

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 -

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 -