PDA

View Full Version : match/copy/paste



ndendrinos
01-01-2011, 06:17 PM
Based on an example found in Contextures from D.Dalgleish this works well (see attachment) .
I'm looking for another way to achieve the same result using just three columns in sheet "Products" (A,B.& C)
Thank you

mikerickson
01-01-2011, 07:24 PM
ndendrinos,

Most people would like a description of the problem before they download a file.

The bald explaination, "Please look in my file and improve it" is not going to get many people interested.

A full description of the goal, data, and desired results, indicating what the difficulties are in addition to a representative attached workbook is the best way to get responses.

ndendrinos
01-01-2011, 08:56 PM
thank you for the response.
I'm not sure how to improve my presentation here. The sample provided (an Invoice) works and the goal is to try to make it work the same way but with a different approach than the one D.Dalgleish uses.
In sheet "invoice" I will type starting in range "A17" a quantity of products ordered. Upon pressing "enter" the cursor will move to the adjascent cell to the right and a userform will appear from which to choose the product.Upon selection the two adjacent cells to the right will auto fill with dollar value for that product as well as the part No of the product as seen on sheet "product" then the cursor will move to "A18" ready for an eventual other quantity of a product.

The difficulty is to use a code that will do this using just three columns in sheet "product" that is columns A,B & C.

I am not looking for an improvement but rather a different approach to a very well thought code.

Hope this will help.

mikerickson
01-01-2011, 11:00 PM
The attached uses native Excel, validation, Names and formulas.
It uses Names:

Name: lastRow RefersTo: =MATCH("zzzz",Products!$A:$A)

Name: Items ReferstTo: =Products!$A$3:INDEX(Products!$A:$A,1+lastRow,1)
Name: ItemHeaders RefersTo: =Products!$A$2:$C$2

Name: ItemData RefersTo: =ItemHeaders:Items

Invoice! B17:B35 have Validation with the List source =Items

C17 has the formula =IF($B17="", 0, VLOOKUP($B17, ItemData, 2, FALSE))
D17, the formula =IF($B17="", "", VLOOKUP($B17, ItemData, 3, FALSE))

E17 has =A17*C17

The formulas are dragged down to row 35.

ndendrinos
01-02-2011, 08:38 AM
Thank you mikerickson your example works fine.
I've come up with an alternative to it like this:

Private Sub lstSelection_Click()
Application.ScreenUpdating = False
ActiveCell.Value = lstSelection.Value
lookfor = Selection.Value
Sheets("Products").Activate
Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
With ActiveCell
Range(Cells(.Row, "B"), Cells(.Row, "C")).Copy Sheets("Invoice").Range("C35").End(xlUp)(2, 1)

Sheets("Invoice").Activate
Unload UserForm1

End With
Range("A35").End(xlUp)(2, 1).Activate
Application.ScreenUpdating = True

End Sub
Badly written I'm sure (maybe someone can help tidy this up)