Extended search macro

[Ingeniero1]

I have to populate several fields of an order log. The log will be in
Sheet-1.
The operator will use the order number to select the data for the log,
and the data will be contained in Sheet-2 and Sheet-3.

(The data in sheets 2 and 3 changes at least once a day, and it varies
in length. The operator generates this data with another program, and
then pastes it onto the XL sheets.)

An order can have several line items, each line item constitutes a
record, and each record contains the order number. More than one record
(line item) could have the same order number

Sheet-2, Column-A contains the order numbers, in no specific order.
Sheet-3, Column-L also contains the order numbers, in no specific
order.

The operator will enter the order number in Column A of Sheet-1, press
a “Button” (runs a macro).
The macro, using the order number in Sheet-1, Column-A, will select and
copy several cells from Sheet-1 and Sheet-2, and paste them on Sheet-1.

QUESTION:
How do I write the macro that will use the number in Column-A to search
through another sheet, locate the row with the same number in Column-A,
copy and paste cells, and continue searching for more occurrences of
the same number in Column-A until the rows with data are exhausted?

Thanks

Alex

 

[Guest]

Alex,
Look at FIND (VBA Help) - this will enable you to search for
occurences of a given Order Number in a given sheet.

Modified VBA Help to give you a starter ....

HTH

----------------------------------------------------------------------------------------------------------------------
OrderNumber=Worksheets"(Sheet1").cells(2,"A")

With Worksheets("Sheet2").Range("A1:A500")
Set c = .Find(OrderNumber, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
r=c.row
...... assign values from row "r" to sheet1 cells.

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With