Lookup multiple values and paste values in adjacent cells

[TimClayton]

Hi all

I can't seem to find the right code for what I want to do. I've designed a very basic POS type system in excel for our shop:

When a stock number is typed into column A, column B,C,D & E are populated using Index & Match formula.
When I press the cash/card/cheque button, Excel copies the information and pastes it in another sheet, then resets the form.
This all works perfectly. However, what I also want to happen when the buttons are pressed is for Excel to copy the date and time at the top, search for all the stock numbers in column A in the "Stock" sheet, and paste it into column I (Date Sold) of all the relevant cells.
I have no idea how to even start this. Below is the current code for the button. It's not pretty, but it does the job:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub CASH()
Range("F19").Select
ActiveCell.FormulaR1C1 = "CASH"

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sale")
Set pasteSheet = Worksheets("Sales")

copySheet.Range("A1:F20").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(3, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(-19, 0).PasteSpecial
Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
Application.ScreenUpdating = True

Application.Goto Reference:="Default"
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Select
End Sub
</code>Any help would be greatly appreciated!

 

[B4andafter]

Hi TimClayton,

Could you please provide a small sample of what the end result would look in the "Stock" sheet?

Thanks.

 

[TimClayton]

Hi, thanks for the reply


Yes the Stock sheet looks like this:

At the moment we manually enter the sold date. I would like the macro to search for the stock numbers entered in the Sale sheet and enter the current date (and time if possible but not necessary) into the Sold column for the specific items.


Thanks!

 

[B4andafter]

Ok Tim, I think this additional lines of code will do the trick (I am not an expert yet but I wish to get there):

Be careful not to overwrite your original code with the copy and the pasting of the additions below- leave an extra space to be sure.

[I][B]'Copy the two lines of code and paste under the "[COLOR=#333333][FONT=Consolas]Dim pasteSheet As Worksheet" code on the original code.[/FONT][/COLOR][/B][/I]
[TABLE="width: 180"]
<tbody>[TR]
[TD]Dim stockSheet As Worksheet[/TD]
[/TR]
[TR]
[TD]Application.DisplayAlerts = False[/TD]
[/TR]
[TR]
[TD][SIZE=2][I][B]'Copy and paste the remaining lines below under the "Set pasteSheet........." line on the original code[/B][/I][/SIZE][/TD]
[/TR]
[TR]
[TD]Set stockSheet=Worksheets("Stock")[/TD]
[/TR]
[TR]
[TD]lastrow=copySheet.Cells(Rows.Count,1).End(xlup).Row[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]For i = 4 To lastrow[/TD]
[/TR]
[TR]
[TD]erow = stockSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row[/TD]
[/TR]
[TR]
[TD]If copySheet.Cells(i, 1) <> “” Then[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,1)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,2)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,3)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,4)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,5)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 9) = copySheet.Cells(1, 1)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 9) = Format(stockSheet.Cells(erow, 9), “dd-mm-yy h:mm”)[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
[TR]
[TD]Next i[/TD]
[/TR]
</tbody>[/TABLE]
[Code/]

Let me know how it goes, good luck.

 

[TimClayton]

Thanks for the reply. Unfortunately the code didn't work.

It didn't search for the stock number and add a date to the sold line.
It added the following to the bottom of the Stock sheet which, apart from the date, is incorrect. I'm not sure why there are 7 dates. I only entered 1 item into the sale.

Also, the code somehow altered my sale template and filled in all the blank cells:

Looking at your code, I can't see anything that suggests that it's "looking up" anything from the sale sheet and finding it on the stock sheet. It just seems to be trying to copy paste to the bottom, which is not what I require.

Just to clarify, I need the code to copy the date from the top cell, LOOKUP any stock numbers entered in the Sale sheet on the stock sheet and then paste the date into the Date Sold column of the relevant row.

Any help is appreciated!

 

[B4andafter]

Sorry about that Tim I didn't match up the columns to the "Sales" sheet which ended up altering it, please update the "For i=4 to lastrow" section as below.


[TABLE="width: 874"]
<colgroup><col></colgroup><tbody>[TR]
[TD]For i = 4 To lastrow 'this assumes that all data entry on the stock # etc. begin on row 4 of the "Sales" worksheet[/TD]
[/TR]
[TR]
[TD]erow = stockSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row [/TD]
[/TR]
[TR]
[TD]If copySheet.Cells(i, 1) <> "" Then 'this line of code checks if cell A4 of the Sales sheet is not empty, if not then it proceeds [/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 1) = copySheet.Cells(i,1)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 2) = copySheet.Cells(i,2)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 3) = copySheet.Cells(i,3)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 4) = copySheet.Cells(i,4)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 5) = copySheet.Cells(i,5)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 9) = copySheet.Cells(1, 1)[/TD]
[/TR]
[TR]
[TD]stockSheet.Cells(erow, 9) = Format(stockSheet.Cells(erow, 9), “dd-mm-yy h:mm”)[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
[TR]
[TD]Next i[/TD]
[/TR]
</tbody>[/TABLE]

Also, if you want the time to show as well on column I, format that column using the format cells function on your Excel ribbon.

This should do it. Thanks.