Products Managment Spreadsheet Problem

[joejoe]

Hello all ,
This is Joseph
i have A problem With Excel 2007 and Programming with it
I have made an Simple Product inventory Excel sheet ..
all what i need in it ...to program a kind of a spreadsheet that lookup for the part number in the inventory and copy all The Row that it exist in
First : the file of my homeinventory is homeinventory.xls
every row contains 8 columns (part number, refrence, ..etc) ..
all what i need is to search for a Single part number and copy its entire row and paste it in another spread sheet or even word sheet ..also ...if this macro can be putten in command button that's will be very nice...
i dunno if there is any way to do that
really i am sorry for my poor english (just self study of english )
also if there any one wants to help me directly ...my email is Removed e-mail address - You can put it in your profile - It's not secure out in the open - Moderator
if you don't understand what i mean ...just write some codes about
1- searching in spreadsheet for a partnumber that user write in Textbox
2- Copying the ENTIRE ROW of the result
3-pasting it in another sheet

and may also i know if i can put this codes in any other sheet (not the homeinventory sheet)

sorry for bothering
Joe


sheet picture

 

[joejoe]

Oh, It Says 0 rows..and Nothing Copied...Or pasted...What is da wrong ??

 

[joejoe]

the macro match the cases ..and it search for strings only.....I mean Only Numbers..i want to search in description

 

[al_b_cnu]

Hi Joe,

So what is your shet layout, and what column is your description in?

Do you have a description such as "Part Number 123456 - Sprocket", and you want to search for '123456'?

 

[joejoe]

Columns Are "A" - "B" - "C'....Yes like what you said.....Such As a cell "bla ble blo " ..i need to search for "bla"...
And i didnt Understand What you mean By The Sheet Layout..
Thanks
joe

 

[al_b_cnu]

Hi Joe, try this macro:

Option Explicit

Sub FindAndCopy()
Const sFindColumn As String = "A"

Dim lFind As Long, lTarget As Long
Dim laRows() As Long, lPtr As Long
Dim lCount As Long
Dim rFind As Range
Dim sFirstAddress As String, sRows As String
Dim sFind As String
Dim vFind As Variant
Dim wsTarget As Worksheet

vFind = Application.InputBox(prompt:="Please enter string to be searched for", _
                            Type:=2)
If vFind = False Then Exit Sub

Set wsTarget = Sheets("Sheet2")

lTarget = wsTarget.Cells(Rows.Count, sFindColumn).End(xlUp).Row
lCount = 0
With ActiveSheet.Columns(sFindColumn)
    Set rFind = .Find(CStr(vFind), lookat:=xlPart, MatchCase:=False)
    If Not rFind Is Nothing Then
        sFirstAddress = rFind.Address
        Do
            lTarget = lTarget + 1
            lCount = lCount + 1
            wsTarget.Rows(lTarget).Value = ActiveSheet.Rows(rFind.Row).Value
            
            Set rFind = .FindNext(rFind)
            If rFind Is Nothing Then Exit Do
        Loop While rFind.Address <> sFirstAddress
    End If
End With

MsgBox lCount & " rows copied."

End Sub

The macro currently searches column A - amend this line as appropriate:

Const sFindColumn As String = "A"