Hello everyone. I have a doozy of a situation here:
On my Inventory sheet I have a table listing all our products, 2500+ items. Column A, Item #, has a mixture of alphanumeric and numeric assigned to each item and that column is formatted as text for the reason of not needing to differentiate between alpha, numerical, alphanumerical, values retaining leading zero's, and preventing scientific notation. When I select any cell on any row, or rows, a shape pops up on the selected row, with an assigned macro, to copy the Item # on the selected row or rows to the first blank row of the Inventory Adjustment worksheet (InvWkSht(code)). The macro adds an apostrophe ( ' ) only to numbers so it would register as text on the Adjustment Worksheet in Column D, row 7 being the first row of data. This works really great and solved the initial problem of a numerical entry. The next column, column B, Item Name, has the formula;
It locates the Item Name and enters it without any issues.
The problem here is, on the Adjustment worksheet if I manually enter a number by typing or with a barcode scanner without prefixing the number with an apostrophe, the formula wont find the item in the Item # column on the Inventory worksheet.
Inventory Table. Item # column
I'm looking for another solution to this scenario that the XLOOKUP would look for the value in Item # on the Inventory sheet whether it's alpha, numerical or alphanumerical values.
How can I do this without formatting the Item # column to the Text format?
Thanks in advance for any energy you exert into this and I totally appreciate it.
Here's the Copy Items To Adjustment WorkSheet Macro:
On my Inventory sheet I have a table listing all our products, 2500+ items. Column A, Item #, has a mixture of alphanumeric and numeric assigned to each item and that column is formatted as text for the reason of not needing to differentiate between alpha, numerical, alphanumerical, values retaining leading zero's, and preventing scientific notation. When I select any cell on any row, or rows, a shape pops up on the selected row, with an assigned macro, to copy the Item # on the selected row or rows to the first blank row of the Inventory Adjustment worksheet (InvWkSht(code)). The macro adds an apostrophe ( ' ) only to numbers so it would register as text on the Adjustment Worksheet in Column D, row 7 being the first row of data. This works really great and solved the initial problem of a numerical entry. The next column, column B, Item Name, has the formula;
Excel Formula:
=IF([@[Item '#]]="","",XLOOKUP([@[Item '#]], Products[Item '#], Products[Item Name], "Item Not Found",0))The problem here is, on the Adjustment worksheet if I manually enter a number by typing or with a barcode scanner without prefixing the number with an apostrophe, the formula wont find the item in the Item # column on the Inventory worksheet.
Inventory Table. Item # column
I'm looking for another solution to this scenario that the XLOOKUP would look for the value in Item # on the Inventory sheet whether it's alpha, numerical or alphanumerical values.
How can I do this without formatting the Item # column to the Text format?
Thanks in advance for any energy you exert into this and I totally appreciate it.
Here's the Copy Items To Adjustment WorkSheet Macro:
VBA Code:
Sub CopyItems2AdjWkSht()
Dim targetRange As Range
Dim visibleCells As Range
Dim targetCell As Range
Dim ws As Worksheet
Dim LastRow As Long
Dim itemCount As Long
Dim copiedItems As String
Dim tbl As ListObject
Set ws = Inventory
Set targetRange = Selection
Set tbl = InvWkSht.ListObjects("InventoryAdj")
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo CleanUp
copiedItems = ""
itemCount = 0
' Check if only one cell is selected. If only one cell is selected, set visibleCells to that cell
If targetRange.Cells.Count = 1 Then
Set visibleCells = targetRange
Else
On Error Resume Next
Set visibleCells = targetRange.SpecialCells(xlCellTypeVisible) ' Applies to a filtered table with hidden rows
On Error GoTo 0
End If
' Check if there are visible cells within the selection
If Not visibleCells Is Nothing Then
For Each targetCell In visibleCells
If Not Intersect(targetCell, ws.Range("A5:G9999")) Is Nothing And ws.Range("A" & targetCell.Row).Value <> "" Then
If InvWkSht.Range("D7").Value = "" Then
LastRow = 7
Else
LastRow = InvWkSht.Cells(InvWkSht.Rows.Count, "D").End(xlUp).Row + 1
End If
'----------------------------------------------------------------------------------------------------
' Problem Snippet
' Copy the cell value as text to avoid scientific notation
' Original attempt:
' InvWkSht.Cells(LastRow, "D").Value = ws.Range("A" & targetCell.Row).Value '
' Next attempt: Added apostrophe to all items copied
' InvWkSht.Cells(LastRow, "D").Value = "'" & ws.Range("A" & targetCell.Row).Value
' Final attempt: Added If to differentiate text and numbers
If IsNumeric(ws.Range("A" & targetCell.Row).Value) Then
InvWkSht.Cells(LastRow, "D").Value = "'" & ws.Range("A" & targetCell.Row).Value
Else
InvWkSht.Cells(LastRow, "D").Value = ws.Range("A" & targetCell.Row).Value
End If
'----------------------------------------------------------------------------------------------------
' Message box information
copiedItems = copiedItems & vbNewLine & ws.Range("B" & targetCell.Row).Value
itemCount = itemCount + 1
End If
Next targetCell
' Adjust row height
tbl.DataBodyRange.Rows.AutoFit
' Display message box with copied items
If itemCount > 0 Then
MsgBox itemCount & " item(s) added to Adjustment WorkSheet:" & vbNewLine & copiedItems, , "Items Added For Adjustment"
Else
MsgBox "No valid items were found to copy."
End If
Else
MsgBox "No visible cells found in the selection."
End If
CleanUp:
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
