# Find value and replace with offset

#### wanabekiwi

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi and welcome to the board!!!
Do you really need Sheet1? You might consider InputBoxes
Code:
``````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

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>

Replies
1
Views
149
Replies
2
Views
81
Replies
11
Views
564
Replies
2
Views
122
Replies
2
Views
102

1,218,617
Messages
6,143,498
Members
450,491
Latest member
Ccruz444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back