dropdown with vlookup by vba

[abdelfattah]

hello
i have data sheet1 in b2 and c2 the brand and the price and result in sheet1 bring data when choose by dropdown from b2:b based on b2 in sheet2 then bring price from sheet2 c2 to col c multiple col a into sheet1 by the way i have data about 1000rows so if do that by vba is better
sheet2

18.xlsm
	B	C
1	brand	price
2	aa	2000
sheet2

result in sheet1 when choose from dropdown

18.xlsm
	A	B	C
1	q	brand	price
2	٢	aa	4000
3	2	aa
4	3	aa
sheet1

Cell Formulas
Range	Formula
C2	C2	=VLOOKUP(B2,sheet2!B2:C13,2,0)*A2

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	List	=OFFSET(sheet2!$B$2;;;COUNTA(sheet2!B:B)-1)

 

[richh]

Not sure what you're trying to do really...

For i = 2 to last Row
   If thisworkbook.worksheets("YoSheet").cells(i,     whateverYourColumnIs) = thisworkbook...Cells(2,2) then
    Price cell = thisworkbook...cells(i, PriceColumn)
    Exit For
Next i

 

[abdelfattah]

hi, richh as you see in sheet1 when choose dropdown in col b it supposes bring price from sheet2 in col c multiple quantity in col a

 

[richh]

Ah ok I see. You may want to use .Target.Offset(0, 1 or -1) rather than static references as i have below. You'll also want to make the function be an after_update on Sheet1 and wrap the code in an If statement that checks to see if the target was in column B something like

If target.column = 2 then...

I can't remember the syntax off the top of my head. Sorry

For I = 2 to lastRow
    If thisworkbook.worksheets("Sheet2").cells(i,1) = thisworkbook.worksheets("Sheet1").cells(2,2) then
        Thisworkbook.worksheets("Sheet1").cells(2,3) = "=A1*" & thisworkbook.worksheets("Sheet2").cells(i,2)
        Exit For
    End If
Next i