VLOOKUP PROBLEM

Question

Hi all,

I'm so stuck.

So basically I'm using a barcode scanner with a userform. Everytime a barcode is scanned the barcode reference appears in the empty cell in the spreadsheet so:

1. Userform, scan barcode.
2. The barcode reference number, example '123', appears in the spreadsheets first column.

However, what I want to be able to do is pull information relating to the barcode number from a second sheet.

So:

If my barcode number is '123' it will show the information relating to the '123' reference on the 2nd spreadsheet on the first spreadsheet.

1st spreadsheet:

BARCODE '123' : NAME 'STEVE' : EXPIRY '12/23'

2nd spreadsheet:

Barcode Ref '123' : NAME 'STEVE' : EXPIRY '12/23'

So to clarify I want the information from the 2nd spreadsheet to appear when ever the barcode relating to the barcode reference is scanned.

I currently am working with a VBA because at the end of spreadsheet 1, I have a timestamp that comes up whenever the barcode is scanned.

Is there a simple way of doing this using a formula or if not how would I do it with a VBA?

I'm a novice, so this is pretty new to me.

I have tried a formula but everytime I do it I get the circular reference problem and im not sure where I am going wrong.

Can anyone help me?

Answer 1

Sorry I changed the sheet names, you can change Sheet2 and Sheet1 to your own sheet names here TIME IN and SPA MEMBER REG. Or you can just check the private message, I have uploaded the sample file.

To make the userform stay in front of all other active windows while being modeless, you can refer to the answers from OssieMac Volunteer Moderator | Article Author, see the thread below:

Keep userform open when opening or closing other workbooks - Microsoft Community

However, this requires you to have some basic knowledge of how to implement it, and the customisation of VBA is somewhat beyond the scope of the current community support, so if possible, please try asking this question on the community I have provided you with, which can be supported more professionally.

Answer 2

Thank you so much for your help.

That is amazing.

Answer 3

Thank you for helping me with this. I very much appreciate it.

Thats exactly what I needed to be honest.

I have deleted sheet 3. That was just a practice sheet to be honest.

I keep getting a Runtime error '9' - Subscript out of range

Could it be because I have a random sheet that I cant seem to get rid of?

Also, sorry to ask but I have been trying to get the userform to stay in front of all other active windows while modeless.

Any ideas?

Answer 4

Hello Tyler-J

Thanks for the file you shared.

I have view the sheet 1 and the sheet 2. Here I can see the Barcode and Customer ID. As show shown below:

If you need to find the corresponding value in the sheet 2, assuming that when you input the value of '123', here I could only take customer ID as your barcode. If you need to change another condition. Just modify the code, here is the result I could make:

If I input the value of 16 (red box), The following value will be input based on the sheet 2, the 16 in the sheet 2 display as shown below:

And I have also make some modifications on your VBA code, here is the code:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)<br>If KeyCode = vbKeyReturn ThenDim emptyRow As LongemptyRow = WorksheetFunction.CountA(Range("A2:A" & Rows.Count)) + 2' Lookup the corresponding information from the second sheetDim barcode As Stringbarcode = TextBox1.ValueDim ws2 As WorksheetSet ws2 = ThisWorkbook.Sheets("Sheet2")Dim found As RangeSet found = ws2.Range("A:A").Find(barcode, LookIn:=xlValues)If Not found Is Nothing ThenCells(emptyRow, 1).Value = barcodeCells(emptyRow, 2).Value = ws2.Cells(found.Row, 2).ValueCells(emptyRow, 3).Value = ws2.Cells(found.Row, 3).ValueCells(emptyRow, 4).Value = ws2.Cells(found.Row, 4).ValueCells(emptyRow, 5).Value = ws2.Cells(found.Row, 5).ValueCells(emptyRow, 6).Value = ws2.Cells(found.Row, 6).ValueEnd IfTextBox1.Value = ""TextBox1.SetFocusEnd IfEnd Sub

But I'm not sure what you need to achieve with sheet 3, this code can be used as a reference, and I've sent the sample file in your private message. Assuming you have more advanced VBA code needs, the following Microsoft Learn community is also suitable for you and is designed to maintain advanced users like you.

Excel - Microsoft Q&A

Best Regards.

Answer 5

Hello Tyler-J

Thanks for posting you issues here.

After reading your description, it is indeed difficult to identify what the cause of the problem is without specific documentation and how the formula is expressed. If you can, please share your VBA code directly or upload a file, this will help us to further identify how to proceed. see here in your private message and attach your file:

Feel free to post back if any updates.

Chandy | Microsoft Community Support Specialist