Copy/Paste with a vlookup

[Sjoerdvk]

Hi guys,

I have been searching alot, but I cannot find the proper solution for my problem. I am new with macros and my knowledge is really basic.

What I need is that; when I press save in sheet two, it saves the factor value and paste this value next to the corresponding code in sheet 1.

I do not think this should be too hard, but I cannot get it to work. Thanks alot for your help in advance.

Sjoerd

 

[Asheesh]

Welcome to the forum...

If you are doing it just for one factor value in sheet 2 then you can do it manually..by using Find option on Sheet1.., but if there is a series of values that you need this macro to work on...can you then please share the sample but exact replica of your requirement and how these two sheets are maintained...

 

[Sjoerdvk]

Hee Asheesh, thanks for your help. No, it will happen for alot of values. So the user will evaluate the factor in sheet 2 with the corresponding graph and data (not in this easy example) and then it needs to be able to save this factor in the 'library' sheet1. This can be up to 300 different factors.

 

[Sjoerdvk]

I noticed that there are similar posts after the same problem, but I was not able to modify that coding toward this problem.

 

[Asheesh]

So do you mean -

Every user may have more than one code which they would need to update the factor for..

In sheet2- Suppose there is a User "X" and this user has several codes in Column B..say 10(B1 to B10)...now this user will manually update the factor against each code say in Column C..i.e (C1 to C10).

Once the user keys in the factors for each code...you want the user to click the save button..and those factors should now get autopulated in sheet 1 in column B..and then pastespecial values.

Is that right? correct my understanding please..

If so, will there be multiple guys using this file at the same time...or is it an individual exercise..?

 

[Sjoerdvk]

Not totally correct if I unsterstand you correctly as well. Sorry that it is not totally clear.

Sheet 1 is your library. I will get a code from there and load that into sheet2. Then I will get all the information that is relevant for that code.

All that information can be adjusted by putting in a factor in the cell d3 in sheet2. Once I feel like I found the correct factor, but manually adjusting this in d3, I would like to be able to save this factor.

So save(copy) this factor and paste it in B:B, next to the corresponding code(the code that is in sheet2.b2 at that moment).

It is an individual exercise.

I hope it is clear now. Thanks for your help!

 

[Asheesh]

Almost...I m on my cell phone right now...I will respond to your query asap tomorrow...apologies for the delay

 

[Sjoerdvk]

No problem, thanks for the help. Trying it myself in the meanwhile.

 

[Marc L]

Hi !

Before to elaborate any VBA code, better is to well know Excel functions !
VLOOKUP in not a Copy / Paste !
And with this kind of worksheet design, it can't operate !
With an Excel better knowledge, your future VBA code will be easier …

MATCH is an appropriate way :

Sub Button1_Click()
    V = Application.Match(Sheet2.Cells(2).Value, Sheet1.Cells(1).CurrentRegion.Columns(1), 0)
    If IsNumeric(V) Then Sheet1.Cells(V, 2).Value = Sheet2.[D3].Value
End Sub

Do you like it ? So thanks to click on bottom right Like !
​