If entered value starts with "W" Move it.

[joepro7]

Hello all,

I am really new to VBA and could use some help on a task i'm working on:

I am trying to code a sheet where if the value entered starts with a "W" it cuts it and moves it to the second-to-next blank column in row 1. I am using a scanner that scans bar codes and immediately presses return when scanned. When I scan codes that start with a "w" I essentially want to start a new column:
sample workbook.jpg

If That W# already exists I want to then move down to the next blank cell in the row that the W# is the header for and make that the new active cell to be able to scan under:
sample workbook2.jpg

Any help would be appreciated, let me know if you need more info.

thank you

[gue2013]

Do you know which events are triggered by the scan input?
If a "Worksheet_Change" or "Worksheet_SelectionChange" is triggered then there will be an easy solution

[joepro7]

Yes, it looks like it triggers a "Worksheet_Change"

[gue2013]

It is not possible to copy the data to a location if you do not know where it should be.
So one possibility is that you always use the same sheet for import scan data check for your "W" and compare with your available data.
Then copy it to another sheet as needed (either adding to available or start a new one) and erase the scan data from the input sheet.

each scan is a single line?

[joepro7]

We should be able to know where to paste the data. It will always go 2 to the right of the last used column in line 1. It would look something similar to this I believe:

"Copy Destination:=Sheet3.Cells(1, a).End(xlToRight).Offset(0, 2).Select.Paste"

so if a W is entered in a10 and c1 is blank it would cut and paste to c1 and i would want c2 to be the next cell that is selected. when the next W value is scanned lets say c7 for example, then it would cut and paste to e1 and e2 would be selected.

I also need to check if the new W value corresponds to one already inputted in A1, C1, E1, and so on. so if we are in e11 and we input a W value that is the same W value as a1 it would remove the value inputted in e11 and not paste anywhere but a10 would be selected because that is the first blank row in the a column.

I know there is a code that we could write to accomplish this logic, I am just very new to VBA. let me know if that makes sense or if it cannot be done as I think.

[gue2013]

still missing info
What data do you get for each scan?
only "W..." ? or
"W.." and one line (two cells) ? or
"W.." and unpredictable number of lines till a next "W.." ?

[joepro7]

for each scan I only get "W.....". the lines without W and just numbers are separate scans of different barcodes. I only want to change columns if a W# is scanned.

[gue2013]

I put the following code into a worksheet, I could not paste it here - a SQL error was thrown, but I don't know why
copy the code into your workbook/worksheet

should do as I understood you

cheers,
Guenter

[joepro7]

Thank You, This is exactly what I was looking for. one small error occurs but it's not a big error. If I rescan an already existing W# and if there is no data underneath it then the code breaks. Thank you for your help!

[gue2013]

exchange

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

then the problem will disappear