Data entry with one constant (but changeable) field

Bowhaven

New Member
Joined
Nov 25, 2016
Messages
6
Hello all,

First time posting so please let me know if I've missed anything and I'll quickly fix it.

I'm looking to set up a sheet where barcodes (4 to 5 digit numbers) will be scanned into Column A. Then, every time a cell in Column A is filled, the adjacent cell in Column B is filled with a number (2 to 3 digit number) determined by a fixed cell, for example D4. I do not want the number in D4 to change for every scan but a simple =IF(A2="","",$D$4) for example won't suffice because the number in D4 will be changed periodically but when it is I would like the previous entries in Column B to keep their assigned numbers.

To further explain the situation, we have lots of barcoded bags which will be scanned as they're thrown into large, numbered crates. I want to set up the Excel file so that the user can simply type in the first crate number into one cell at the start of the session then scan bags as they throw them into the crate. Then, once that first crate is full, the user can then type in the number of the next crate and continue to scan and throw bags into that new crate. Therefore, at the end of the day I can look at the Excel file and it will show me which crate each individual bag is in.

I'm using Excel 2013 on Windows 7 Enterprise.

I'm sorry if I haven't explained it well enough but I've been going mad trying to learn VBA/userform stuff all day which is potentially overcomplicating things.

Cheers,

Chris
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Right Click the sheet, and view code. We don't want to add this to a module, we want to add this code to the worksheet that needs to trigger it. Every time the last cell in column A is updated, it copies the value from D4. If D4 changes, the old values do not, but any new entries will copy D4's value. I have the range trigger set to rows 2 through 10,000, but that can be changed if you're dealing with more than that. This assumes that the last row is where data is going, and it will only update the B column if it's blank.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim ALastRow As Long
    Set KeyCells = Range("A2:A10000")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        With ActiveSheet
            ALastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        If ActiveSheet.Cells(ALastRow, 2).Value = "" Then
            Range("$D$4").Copy
            Range("B" & ALastRow).PasteSpecial xlPasteValues
            Range("A" & ALastRow+1).Select
        End If
    End If
End Sub
 
Last edited:
Upvote 0
Right Click the sheet, and view code. We don't want to add this to a module, we want to add this code to the worksheet that needs to trigger it. Every time the last cell in column A is updated, it copies the value from D4. If D4 changes, the old values do not, but any new entries will copy D4's value. I have the range trigger set to rows 2 through 10,000, but that can be changed if you're dealing with more than that. This assumes that the last row is where data is going, and it will only update the B column if it's blank.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim ALastRow As Long
    Set KeyCells = Range("A2:A10000")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        With ActiveSheet
            ALastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        If ActiveSheet.Cells(ALastRow, 2).Value = "" Then
            Range("$D$4").Copy
            Range("B" & ALastRow).PasteSpecial xlPasteValues
            Range("A" & ALastRow+1).Select
        End If
    End If
End Sub
Wouldn't this overwrite the last entry
Code:
Range("B" & ALastRow).PasteSpecial xlPasteValues
since the variable ALastRow is set to the last row with data and no offset?
 
Upvote 0
ALastRow is the last row in column A. With a brand new entry, B will be blank. It checks to make sure it's blank (.value="") before writing the data.
 
Last edited:
Upvote 0
Thank you so much Derek73, you're a lifesaver! You understood my problem perfectly. A2:A10000 should be more than enough but I can see where it is in your code if I need to change it. Thanks again because it also helps me learn to solve problems myself by seeing how you've used code to solve it.

Thanks to JLGWhiz too for having my back and making sure it's good to use.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top