macro to upgrade cell values!!!

[ckmoied]

I have some item number in col A, item names in col B, and quantities in Col C, of sheet 1.

Now in sheet 2, col A , I enter any of the part numbers, such that col B takes the name automatically as I used VLOOKUP function. However I enter the new quanitity for this item in Col C.

Now I need a macro such that once run this macro, the corresponding value in sheet 1 must upgrade to the new quantity (as entered in sheet 2) for the specific item only.

any suggestion

 

[gnaga]

I have some item number in col A, item names in col B, and quantities in Col C, of sheet 1.

Now in sheet 2, col A , I enter any of the part numbers, such that col B takes the name automatically as I used VLOOKUP function. However I enter the new quanitity for this item in Col C.

Now I need a macro such that once run this macro, the corresponding value in sheet 1 must upgrade to the new quantity (as entered in sheet 2) for the specific item only.

any suggestion

Copy this code into your Sheet2 module and try to change the quantity in sheet2 column C and see the desired result getting or not
<Pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range
If Target.Column = 3 Then
Set rng1 = Worksheets("Sheet1").Range("A1:A" & Range("A65536").End(xlUp).Row)
Set rng2 = rng1.Find(Target.Offset(0, -2))
rng2.Offset(0, 2).Value = Target.Value
End If
End Sub</Pre>

GNaga

 

[ckmoied]

Sorry, the suggested macro did not work. Maybe I am mistaken about the method of adding the module in the worksheet. Can u please suggest the step wise method of addign the module to the worksheet.

thanx.

 

[gnaga]

..... Can u please suggest the step wise method of addign the module to the worksheet.

thanx.

Right click the leftmost excel icon (on the left side of File Menu) and click view code.

Underneath of the Microsoft Excel Object tree double click Sheet2 and then paste this code. Then try.

GNaga