Find value and replace with offset

[wanabekiwi]

Hi'
i'm new to this macro stuff and have been slowly working through some of the Mr excel lesons i have a problem / question i have a price list which i want to make an updater for. I need some help on find and referncing a cell?
So i want to write a macro to find the product code i enter into say cell "A2" on "Sheet 1" in colum A on "Sheet 2" and then take the new price from say "B2" on "Sheet 1" and paste it into the 4 colum cell which relates to the find in "sheet 1"
i am sure that is very bad english i hope you understand.
the actual problem is a bit more complex but once i understand the basics i will sort the rest outr my self
cheers for any help

 

[lenze]

Hi and welcome to the board!!!
Do you really need Sheet1? You might consider InputBoxes

Sub Update()
Dim myCode As String
Dim myPrice As Variant
Dim C As Range
myCode = InputBox("Enter the code!")
myPrice = InputBox("Enter the new price")
With Sheet2.Range("$A$2:$A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Set C = .Find(myCode, LookIn:=xlValues)
    If Not C Is Nothing Then Cells(C.Row, 4) = myPrice
End With
End Sub

lenze

 

[wanabekiwi]

Hi Lenze<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for that. The reason i wos working on 2 worksheets is 1 holds the price list the second has the checking updater bit. I can type in the part code and it goes off and gets me the current info on price, discount etc from the price list. i can then check this off against my suppliers invoice and if pricing has changed i can A) ask the supplier why<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_1 alt="0" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="0" src="file:///C:\DOCUME~1\Nigel\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>and B) enter the new pricing structure and then have a button linked to the macro which will then go and update the pricing. Having said that yours works perfectly well i just need to tweak it to work with my sheet. thanks very much for your help<o></o>