为什么我的Application.XLOOKUP无法匹配数字值?

huangapple go评论63阅读模式
英文:

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

  1. 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.

为什么我的Application.XLOOKUP无法匹配数字值?
The LookupSheet. Just as the picture above the datatype of the numeric and other Values are General.
为什么我的Application.XLOOKUP无法匹配数字值?

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

为什么我的Application.XLOOKUP无法匹配数字值?

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

huangapple
  • 本文由 发表于 2023年7月17日 17:42:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76703206.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定