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]

thanks very Much alan, thanks for the reply
i put the code in the "ActiveSheet" sheet , and restarted the spreadsheet , enabled macros...but the problem is that i dunno how to make the Code works...i readed your post ..you say double click the cell ...what cell? .i tried to D-click on a non-embty cell ...but it didnt't append it's row....Dunno why /Something wrong went with the code...... I need to A msgbox To put the part number in ..then it search for those cells...and then append them to "Sheet2" .... That's Alll....To be more flexible in searching for items...thanks alan
Sorry for bothering
Joe

 

[al_b_cnu]

Hi Joe,

Try double-clicking the cell that contains the part number that you want copying to Sheet2

 

[joejoe]

Thanks For the fast reply....whuuuh...
Is it possible to Just Make It append Rows Of a Word that I write In Msgbox?...Or it isn't ?.....i mean ...to be more flexible in searching for A WORD, because your code just search for the same value of the cell (by matching case) ..i need it to match entire cell content ...
thanks very much again
..

 

[al_b_cnu]

Hi Joe,

could you not just use the code I posted earlier - just write a new macro (or place in a CommandButton) to call 'SelectRows' followed by a call to 'CopySelectedRows'

Are your part numbers numeric, or text?

 

[joejoe]

thats what i was making b4 da last code...but i need to automatic copy all the rows that contains the part number...like the last code...but i mean ..i need the last code with a msg box asking for the partnumber to append it's rows...and 2 match entire cell content.....So...i mean Now i am looking for using the last code ...So.,.How can I just Make the last code Match The entire Cell Content ..
thanks Very Much
Joe

 

[joejoe]

I think I am user of Lot questions...Sorry For that....

 

[al_b_cnu]

thats what i was making b4 da last code...but i need to automatic copy all the rows that contains the part number...like the last code...but i mean ..i need the last code with a msg box asking for the partnumber to append it's rows...and 2 match entire cell content.....So...i mean Now i am looking for using the last code ...So.,.How can I just Make the last code Match The entire Cell Content ..
thanks Very Much
Joe

Hi Joe,

The code supplied should match on the entire cell contents (the 'Lookat:=xlWhole' should do this).

Alan

 

[joejoe]

Can I change The code from the start ..to Depend on a msg Box...Not The selected cell...or Maybe An textbox..anything like that?

 

[joejoe]

It match the case...I tried...You can try and see..

 

[al_b_cnu]

Hi Joe,

How about this macro:

Option Explicit

Sub FindAndCopy()
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")

If IsNumeric(vFind) Then
    lFind = CLng(vFind)
Else
    sFind = CStr(vFind)
End If

lTarget = wsTarget.Cells(Rows.Count, "A").End(xlUp).Row
lCount = 0
With ActiveSheet.Columns("A")
    If IsNumeric(vFind) Then
        Set rFind = .Find(CLng(vFind))
    Else
        Set rFind = .Find(CStr(vFind), lookat:=xlWhole, MatchCase:=False)
    End If
    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