VBA: need help matching range values to each other

[Lil Stinker]

Need help with VBA to match/search/find values in a range on one sheet with the same values in another range on a second sheet and input data when the match is found.

Sheet1"Receipt" is a form where multiple serial numbers are entered into the Range B10:S90. (Serial numbers are alphanumeric and sometimes start with 0)
Sheet2"Log" is where I have all of the existing serial numbers stored (as text) in Column B.

The task is to loop through every serial number on Sheet1 and match them to Sheet2. Provided there is a match, enter the date found on Sheet1 cell A1 into Column E on Sheet2 of the matching serial number row. If no match, alert user serial number needs to be entered into inventory.

It seems simple enough for an experienced VBA user but I'm not that.

I would appreciate any help you can provide.

 

[DanteAmor]

Hi @Lil Stinker
I hope you are well.

There are a couple of things that don't make sense to me.To make it easier to understand your problem.
Could you put some images or a minisheet of what you have on your sheets before the macro?
And what result do you want after the macro?
Please, put several examples 10 or 15.​

​

And this is not clear to me:

If no match, alert user serial number needs to be entered into inventory.

How do you want the alert?

Explain everything with examples.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Lil Stinker]

Hi Dante,
Thanks for the reply. So, Sheet1 is where we input the serial numbers for equipment that is going out on rental. It can vary from a few items to hundreds so I didn't include the entire range where the numbers are entered because most of the time, the range will have blank cells. The full range would be B10:O90. I will have conditional formatting set up to call out any duplicate entries prior to running the macro otherwise, every serial number is unique.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2/3/2023	Date Out		2/13/2023	Date In		Location	OC Carnival Event
2
10		SERIAL NUMBERS
11		00011	00099	0123	908	999	1000	1001	1002	1234	1245	01890	11111	12456	50055
12		50555	50600	50653	50654	50655	1324	4654	4667	0047	8897	89897	1321	1357	1351
13		13248	46513	305	13121	32132	8468	3215	35133	01348	65679	00546	0056	1100	12346
14		100	1011	12121	2113	54546	11150	1500	1456	56465	25656	44557	555	50666	50750
15
16
17
18
19
20
Sheet1

Sheet2 is our inventory sheet where all 2500 serial numbers are stored for each item. What I would like is for the inventory to be updated regularly when the macro is run based on the numbers being logged out on Sheet1. If the number appears on Sheet1, it means on Sheet2, the status should be set to Out, the date out and the location should be recorded from Sheet1 into the corresponding columns on Sheet2. Basically, resembling the last few rows where I've input the data manually.

Book1
	A	B	C	D	E	F	G	H
1	Item ID	SerialNumber	Model	Description	Status	Location	DateOut	ReturnDate
2	1	00011	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
3	2	00099	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
4	3	0123	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
5	4	908	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
6	5	999	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
7	6	1000	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
8	7	1001	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
9	8	1002	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
10	9	1234	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
11	10	1245	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
12	11	01890	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
13	12	11111	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
14	13	12456	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
15	14	50055	Motorola Radius CP200	UHF 16 Channel Walkie	IN	Warehouse
16	15	213	Motorola Charger	Multi Unit Charger	OUT	OC Carnival Event	2/3/2023
17	16	305	Motorola Charger	Multi Unit Charger	OUT	OC Carnival Event	2/3/2023
18	17	250	Motorola Charger	Multi Unit Charger	OUT	OC Carnival Event	2/3/2023
19	18	832B	Power Supply	Power Supply	OUT	OC Carnival Event	2/3/2023
20	19	1019	Power Supply	Power Supply	OUT	OC Carnival Event	2/3/2023
21	20	1321	Power Supply	Power Supply	OUT	OC Carnival Event	2/3/2023
22	21	1804	Power Supply	Power Supply	OUT	OC Carnival Event	2/3/2023
Sheet2

As for the alert, just a simple MsgBox pop-up alerting the user that the serial number was not found in inventory is all I'm looking for.

Thanks for taking the time!

 

[DanteAmor]

Hi Lil
Thanks for posting the examples.

Try this macro:

Sub out_serial_number()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant, c As Variant
  Dim dic As Object, i&, j&
  Dim dt As Date, lo As String
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = sh1.Range("B11:O" & sh1.Range("B:O").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row).Value
  b = sh2.Range("B2:B" & sh2.Range("B" & Rows.Count).End(3).Row).Value
  c = sh2.Range("E2:G" & sh2.Range("B" & Rows.Count).End(3).Row).Value
  
  dt = sh1.Range("A1").Value
  lo = sh1.Range("H1").Value
  
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      If a(i, j) <> "" Then dic(a(i, j)) = Empty
    Next
  Next
  
  For i = 1 To UBound(b, 1)
    If dic.exists(b(i, 1)) Then
      c(i, 1) = "Out"
      c(i, 2) = lo
      c(i, 3) = dt
    End If
  Next
  
  sh2.Range("E2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DanteAmor]

I forgot the alert message, better try this version:

Sub out_serial_number_v2()
  Dim rng As Range, c As Range, f As Range
  Dim dt As Date, lo As String, cad As String
  
  With Sheets("Sheet1")
    Set rng = .Range("B11:O90")
    dt = .Range("A1").Value
    lo = .Range("H1").Value
  End With
  
  For Each c In rng
    If c.Value <> "" Then
      With Sheets("Sheet2")
        Set f = .Range("B:B").Find(c.Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          .Range("E" & f.Row).Value = "OUT"
          .Range("F" & f.Row).Value = lo
          .Range("G" & f.Row).Value = dt
        Else
          cad = cad & c.Value & vbCr
        End If
      End With
    End If
  Next
  If cad <> "" Then
    MsgBox cad, , "Serial number was not found in inventory"
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Lil Stinker]

Wow! Both worked exceedingly well on my sample data set. I will try plugging in the second version into my workbook tomorrow and see if any issues pop up. I prefer using code I can understand in my limited capacity and I can get an idea of what's happening in version two. Version one is way over my head! Reason being, I may need to manipulate this later on as needs change.

I will get back to you tomorrow with the results. Thank you!

 

[Lil Stinker]

Hello Dante,
The second version works great! I'm still learning VBA so, if you don't mind, what does cad represent?

cad = cad & c.Value & vbCr

If cad <> "" Then MsgBox cad, , "Serial number was not found in inventory"

If I wanted to add more of a message to the MsgBox, how would I go about doing that? For instance, if I wanted the MsgBox Title to say "Not Found" and the body of the MsgBox to say "The following serial numbers were not found in inventory:" followed by the list of serial numbers (or cad)... is this possible?

 

[DanteAmor]

what does cad represent?

"cad" is just the name of a variable which will store a text.

Try:

MsgBox "The following serial numbers were not found in inventory:" & vbCr & cad, , "Not Found"

 

[Lil Stinker]

Now for the tricky part. When it comes to returning the equipment to inventory, the form design is the same however, I have a macro set up to change the color of the serial number cells to indicate their returned status. For example, new returns will be colored yellow, damaged returns colored red, previous returns colored gray and serial numbers that are still out have no cell color but the font is gray.

How do I add to or change version 2 (in a new Sub) to update Sheet2 to say "IN" in column E for yellow colored cells on Sheet1, "DMG" in column E for red cells and ignore any gray cells or non-colored cells?

Here's a sample:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2/3/2023	Date Out		2/13/2023	Date In		Location	OC Carnival Event
2
10		SERIAL NUMBERS
11		345	0647	0654	654	0654	1020	1058	1657	3165	3230	3245	3251	3545	04657
12		04657	4684	4987	6513	6540	6545	6546	6548	6548	6565	6813	6878	6878	07898
13		7987	8787	8987	9873	13151	13515	16511	32151	34516	35408	35484	38541	45645	46578
14		46878	50654	7894	8183	9504	9545	10500	11050	12005	18456	25452	25453	65006	80000
15		5500	6500	94658	85858
16
17				-> previously marked in returns (can be ignored or empty)
18				-> new returns marked in (should be marked "IN" on Sheet2)
19				-> new return but damaged (should be marked "DMG" on Sheet2)
20				-> numbers with no coloring indicate not returned status (can be ignored or empty)
21
Sheet1

 

[DanteAmor]

Hi @Lil Stinker .
These are new requests, please create a new thread.
There it explains in detail what you need, you put your macro to modify it or in a new macro they help you with what you need now.