Macro to find value and add data in adjacent cell

[TimClayton]

Hi all

I'm trying to find a code that will find all values in a range and add another value to an adjacent cell. Example below:

This is a slightly simplified version of what I'm using, but the principle will be the same. The "stock#" & "sold" columns are further apart and the stock numbers would be a mix of letters and number, such as "2H176".

In my head the code would do the following:

Copy value from column G to column D for each value in column F found in column A

So, maybe something like this:

Set lookup values as range F1:F100
Find and replace lookup values in range A1:A100 with data from range G1:G100 (offset 3 colums)

I can't seem to find anything to do the job.

Any help is appreciated!

 

[WaterGypsy]

I'm probably misunderstanding your requirement , but does this do it?

=VLOOKUP(A2,F:G,2,FALSE)

 

[TimClayton]

I'm probably misunderstanding your requirement , but does this do it?

=VLOOKUP(A2,F:G,2,FALSE)

Thanks for the suggestion. This does the job to a certain extent. If I paste this into column D, it fills in the correct date. However, the lookup table will be scrubbed every week. I need something that permanently adds the dates.

If I were to use this method, I can only think that I would have to copy and paste values to make them permanent, and then replace all the #N/A values with the formula again. There's 4000+ items of stock. I guess it wouldn't take too long, but I'm looking for a macro to do the job in one click ideally.

I'll keep this as a backup method!

 

[WaterGypsy]

Do you need column D to show the latest date from G that the Stock number appeared in F over time?


If that is the case and you can use a helper column(e.g col E ) the use this code to copy Col D to E

    Columns("D:D").Select
    Selection.Copy
    Columns("E:E").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

and change the formula to

=IF(ISNA(VLOOKUP(A2,F:G,2,FALSE)),E2,VLOOKUP(A2,F:G,2,FALSE))

 

[TimClayton]

Thanks for the suggestion again.

There is only ever 1 of each product, so there will never be repetition of stock numbers and therefore no "latest date". There will only be 1 date associated with each stock number.

Over the course of a week, details of sales will appear in the "Weekly Sales" sheet. I then extract the stock numbers and sale dates into the format in columns F & G.

The idea is to then press a button and excel finds all the stock numbers and copies the dates over to the sold column next to each one. Then I can just delete/archive the contents of columns F & G until the next week.

At the moment, the first suggestion is the closest. It will just require some sort of manual reset at the end of the week.

Your help so far is very much appreciated!

 

[TimClayton]

I still haven't found a way to do this. If anyone has any other suggestions, that would be great!