Find value and replace with offset

wanabekiwi

New Member
Joined
Jul 19, 2010
Messages
2
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

wanabekiwi

New Member
Joined
Jul 19, 2010
Messages
2
Hi Lenze<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><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:p></o:p>
:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,147
Messages
5,835,667
Members
430,373
Latest member
swartzfeger

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top