英文:
Why can't my Application.XLOOKUP match numeric values?
问题
我的Excel VBA函数适用于文本值,但当查找值仅包含数字时,XLOOKUP找不到匹配项。
Range(SelectedCell) = Application.XLOOKUP("*" & Range(LookupCell) & "*", Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstRefRange.Address), Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstMPNCol.Address), "NOT FOUND", 2)
我尝试过为查找值和搜索数组添加格式化函数,并尝试将单元格数据存储为数字、文本和常规格式。此外,我还尝试在XLOOKUP函数的查找值和查找数组部分添加Format函数。
当我手动在其中一个工作表的单元格值上执行XLOOKUP时,函数按预期工作。
我做错了什么,该如何修复?
英文:
My Excel VBA functions works on text values but when the lookup value contains numbers only, the XLOOKUP can't find a match.
Range(SelectedCell) = Application.XLOOKUP("*" & Range(LookupCell) & "*", Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstRefRange.Address), Worksheets("Stuklijst").Range(Mod_SelectColumns.StuklijstMPNCol.Address), "NOT FOUND", 2)
I tried adding a format function to the Lookup Value and Search Array and I tried storing the cell data as a Number, Text and General. Next to that I tried adding a Format function to the lookup value and lookup array part of the Xlookup function.
When I execute an XLOOKUP manually on the Cell values in one of the sheets, the function works as expected.
What am I doing wrong and how can I fix it?
Edit: I replicated my issue with some generated data
- Original data before executing the XLOOKUP the Articenumber column is empty. The numeric values are in the General Number format, such are the other values.
The LookupSheet. Just as the picture above the datatype of the numeric and other Values are General.
As you can the XLOOKUP function can't find the numeric values and results in "NOT FOUND" (Yes I have checked for spaces at the end of value).
The code I use to find the Articlenumbers in the LookupSheet (the button calls the subroutine):
Sub XLOOKUPTEST()
Dim rangeMpn As Range
Dim rangeArtikel As Range
Dim rangeLookupArtikel As Range
Dim rangeLookupMPN As Range
Dim selectedCell As String
Dim lookupCell As String
Dim rowCount As Integer
Dim i As Integer
Set rangeMpn = Worksheets("Resultsheet").Range("B2:B15")
Set rangeArtikel = Worksheets("Resultsheet").Range("C2:C15")
Set rangeLookupArtikel = Range("A2:A8")
Set rangeLookupMPN = Range("B2:B8")
rowCount = 14
For i = 2 To rowCount
selectedCell = "C" & CStr(i)
lookupCell = "B" & CStr(i)
Range(selectedCell) = Application.XLookup("*" & Range(lookupCell) & "*", Worksheets("LookupSheet").Range("A2:A9"), Worksheets("LookupSheet").Range("B2:B9"), "NOT FOUND", 2)
On Error Resume Next
If Range(selectedCell) = "" Then
Range(selectedCell) = "FOUND BUT NULL"
End If
Next i
End Sub
答案1
得分: 3
根据您的数据,我不明白为什么您需要通配符查找。
此外,XLOOKUP
可以返回一个数组,因此无需逐个处理每个单元格。
一般来说,如果尽量减少对工作表单元格的访问,只在 VBA 中进行处理,VBA 程序运行速度会更快。在这个特定情况下,由于 XLOOKUP 将返回一个数组,我不确定哪种方法更快,但我已经展示了“在 VBA 中处理”的例程。
对于非通配符的 XLOOKUP
,您可以适应以下代码:
Option Explicit
Sub due()
Dim vLookupVal As Variant
Dim vLookupArr As Variant
Dim vLookupRes As Variant
Dim vArtNum As Variant
Dim v As Variant
Dim rArtNum As Range
With ThisWorkbook.Worksheets("Sheet1")
vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With
rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND")
End Sub
如果您需要通配符,但在您的示例中没有明显原因,那么可以修改以下部分:
- 将
vLookupVal
数组设置为文本并包含通配符 - 将
vLookupArr
数组设置为文本 - 修改
XLOOKUP
函数以执行通配符搜索
Option Explicit
Sub due()
Dim vLookupVal As Variant
Dim vLookupArr As Variant
Dim vLookupRes As Variant
Dim vArtNum As Variant
Dim I As Long
Dim rArtNum As Range
With ThisWorkbook.Worksheets("Sheet1")
vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With
For I = 1 To UBound(vLookupVal, 1)
vLookupVal(I, 1) = "*" & CStr(vLookupVal(I, 1)) & "*"
vLookupArr(I, 1) = CStr(vLookupArr(I, 1))
Next I
rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND", 2)
End Sub
英文:
Given your data, it is not apparent to me why you need wild card lookups.
Also XLOOKUP
can return an array, so there is no need to process each cell individually.
In general, VBA routines run faster if one minimizes the access to the worksheet cells, and does the processing solely in VBA. In this particular instance, since XLOOKUP will return an array, I'm not certain which method would be faster, but I have shown the "process in VBA" routine.
For a non-wild card XLOOKUP
you could adapt the following:
Option Explicit
Sub due()
Dim vLookupVal As Variant
Dim vLookupArr As Variant
Dim vLookupRes As Variant
Dim vArtNum As Variant
Dim v As Variant
Dim rArtNum As Range
With ThisWorkbook.Worksheets("Sheet1")
vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With
rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND")
End Sub
If you require the wildcard for some reason not apparent in your example, then modify
- the
vLookupVal
array to be text and include the wildcards - the
vLookupArr
array to be text - the
XLOOKUP
function to do a wild card search
Option Explicit
Sub due()
Dim vLookupVal As Variant
Dim vLookupArr As Variant
Dim vLookupRes As Variant
Dim vArtNum As Variant
Dim I As Long
Dim rArtNum As Range
With ThisWorkbook.Worksheets("Sheet1")
vLookupVal = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
vLookupArr = Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
vLookupRes = Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp))
Set rArtNum = .Cells(1, 2).Resize(rowsize:=UBound(vLookupVal))
End With
For I = 1 To UBound(vLookupVal, 1)
vLookupVal(I, 1) = "*" & CStr(vLookupVal(I, 1)) & "*"
vLookupArr(I, 1) = CStr(vLookupArr(I, 1))
Next I
rArtNum = Application.XLookup(vLookupVal, vLookupArr, vLookupRes, "NOT FOUND", 2)
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论