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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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>
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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