Cut entire cell, based on specific text in cell, and paste to new cell

Question

I have a six-column table in excel that I am using with a barcode scanner. We are scanning custom QR codes that display text into each cell. The scan starts with scanning the employee's QR code on their badge followed by the specific item(s) that are being checked out or in. The table's range is B2:G2. Column B is labeled Employee #, Columns C-F are labeled ITEM #1-4 and the 6th column and final column is labeled IN/OUT. The scanner we use tabs through each cell after the custom text is scanned and entered in each cell.

I would like to cut an entire cell, based on the specific text IN or OUT, and paste it to a new cell. For example, if in columns D-F the barcode scanner enters IN or OUT, I want to cut the IN or OUT value from the cell and paste it into column G, plus automatically tab to the following table's row and cell in column B after the paste occurs. Column B and column C should never have text containing IN or OUT.

Is this possible? I found a similar question dealing with multiple workbooks and cutting entire rows instead of cells, so I figured this might be achievable as well.

I experimented with different methods of VBA, macros, and formulas in excel, but none of them could give us what we wanted. Our goal is to limit the number of scans needed. If a person comes in and scans out all 4 items, then there will be no need to cut and paste IN or OUT. After 4 items are scanned the scanner will scan the QR code with the text IN or OUT into column G and automatically tab to column B. However, a majority of our people only check out 1, 2, or 3 items and we want to eliminate any additional "Blank" scans or having to go over to the computer to press tab or click in a new cell. The columns where this will occur will always be B-G but will need to continue for each row (B2:G2, B3:G3, B4:G4) We would be using at least 600 rows for the approximately 300 people we have.

My experience writing code is limited to say the least. Any code recommendations or suggestions would be greatly appreciated.

Answer 1

You can write a macro that triggers "OnEnter" or "OnTab" (using Application.OnKey "{ENTER}" (or "{TAB}", whichever), "ThisMacro") — which will then run EVERY time Enter is pressed for any reason — it would check the column the currently selected cell is in and if not a column D-F cell, it would End. When a column D-F cell is the currently selected cell, it would evaluate the content of said cell. If not "In" or "Out" it would End. But if it is "In" or "Out" it would cut the value and move to the column G cell of the row, then paste the cut value. To finish, it would select the column B cell in the next row leaving you ready to scan the next employee ID.

I do not write these macros myself, never had a need. So various not-immediately-obvious concerns could arise and need addressed. For instance, in the above I blithely say "would End" a couple times. Perhaps it interrupts BEFORE doing the operation Enter would normally do. Perhaps not. If not, the blithe "would End" is the end of that. But if so, then Enter's normal effect would not occur and you'd have to insert a line in the macro to DO the normal operation, i.e., move over a cell, or move down a row and back to column B. (It's not clear how you accomplish THAT, moving down a row and back to column B... one thought is this data is in a formal Excel Table and so upon reaching the right edge, column G, TAB moves down a row and and to the left edge, or perhaps it's old school and a macro selects (and maintains this selection) the range so that TAB (or Enter) will act the same way the Table provides for.) So there's that level of consideration too, that of how this would interact with said macro. And other things might arise as well. However, all should be fairly trivial to deal with. Tedious, to be sure, and detail work, but still trivial, especially since you are comfortable with macros already.

Since the work in question would not involve heavy use, the fact the macro would run every time the chosen key is pressed or "pressed" should not be the least concern. A bit different from using such an event capturer in a spreadsheet in which you'll be pressing Enter 2,000 times a day.

(But maybe not even then. It would be short and VBA is blindingly fast when just running code, not performing a few hundred, thousand, or tens of thousands of "read this cell, do these things, write to that cell" operations in a loop.)

(If I wrote these now and then, I'd provide sample code to modify. I don't though, so I'd likely only mislead you. If I haven't already(!) though I've seen examples over time doing all the things I've mentioned above. But seeing them, and doing them (successfully), are two pretty different things. Writing, and cobbling the pieces together, should be straightforward, and examples of each bit easy to find on the internet if you don't know each bit already, so I don't feel like I'm teasing you with an idea while providing no substance.)

Answer 2

Here is the code that was sent to me, and it is working exactly how I needed. Pasting it as an answer for anyone else looking for something similar.

Private Sub Worksheet_Change(ByVal Target As Range)
'check if only one cell is changed
If Target.CountLarge > 1 Then Exit Sub
'check if target is in columns D, E, or F
If Intersect(Target, Range("D:F")) Is Nothing Then Exit Sub
'check if target value is capital "IN" or "OUT"
If Target.Value <> "IN" And Target.Value <> "OUT" Then Exit Sub

Application.EnableEvents = False             'prevent triggering another change event
Target.Cut Destination:=Cells(Target.Row, 7) 'cut and paste in column G
Cells(Target.Row + 1, 2).Activate            'move active cell to next row, column B
Application.EnableEvents = True              'reenable events
End Sub