检查一个字符串是否在行中，并使用 application.vlookup 检索坐标

Question

场景：我正在构建一个代码，该代码从工作表中读取一些输入，在其他两个工作表中找到等效项，如果数据可用，则对其进行一些操作。

数据：

Worksheet 1 (CAs):
name    ident    date       value1    value2
C       xxx      xx/xx/xx    5000      100
T       YYY      xx/xx/xx    1000      101
and so on, there are more than 2 thousand identifiers (ident) in sheets A and B, while in sheet CAs only 10 of those appear.
Worksheet 2 (A): Monthly dates
Ident 1     Ident 2    ...    Ident N     row number
20/01/01      1            1                  1             2
20/02/01      1            1                  1             3
20/03/01      1            1                  1             4
...
20/12/12      1            1                  1             N-1 
col number    2            3                  N              N
Worksheet 3 (B): Daily dates
Ident 1     Ident 2    ...    Ident N     row number
01/01/01      1            1                  1             2
02/01/01      1            1                  1             3
03/01/01      1            1                  1             4
...
12/12/12      1            1                  1             N-1 
col number    2            3                  N              N

代码的作用：它读取工作表 1，获取日期和标识符，使用查找过程在其他工作表中查找该标识和日期的坐标，并获取交点处的数据。

问题：出于某种原因，当我输入要读取的 IsInArray 函数的标识符时，它总是返回 False，尽管它肯定在标识符数组中。

问：知道我在这里做错了什么吗？

法典：

Sub Calculations()
Dim lrow As Long, i_number As String, lastRow As Long, lastCol As Long, datefinalmatch As Long, datefinalmatch2 As Long, z1 As Long, ifinalmatch As Long
Dim lastColLetter As String, a As String
Dim p_number As Variant, amount_number As Variant, aaa As Long, bbb As Long
Dim date_number As Date
Dim wb As Workbook
Dim ilist As Variant
Set wb = ThisWorkbook
For lrow = 2 To wb.Sheets("CAs").UsedRange.Rows.count
i_number = wb.Sheets("CAs").Range("B" & lrow).Value
date_number = wb.Sheets("CAs").Range("C" & lrow)
p_number = wb.Sheets("CAs").Cells(lrow, 5)
amount_number = wb.Sheets("CAs").Range("D" & lrow)
lastRow = wb.Sheets("A").Cells(Rows.count, 2).End(xlUp).Row
lastCol = wb.Sheets("A").Cells(2, Columns.count).End(xlToLeft).Column
lastColLetter = Col_Letter(lastCol)
ilist = wb.Sheets("B").Range("B1:" & lastColLetter & "1")
datefinalmatch = Application.VLookup(wb.Sheets("CAs").Cells(lrow, 3), wb.Sheets("AMT").UsedRange, lastCol, True) + 1 'row
If IsInArray(i_number, ilist) = True Then
ifinalmatch = Application.HLookup(i_number, wb.Sheets("A").UsedRange, lastRow, False) 'column
'other sheets date row finder
datefinalmatch2 = Application.VLookup(wb.Sheets("CAs").Cells(lrow, 3), wb.Sheets("B").UsedRange, lastCol, True) + 1 'row
End If
Next lrow 
End Sub
Private Function IsInArray(valToBeFound As String, arr As Variant) As Boolean
Dim element As Variant
For Each element In arr
If element = valToBeFound Then
IsInArray = True
Exit Function
End If
Next element
End Function
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

OBS：代码运行流畅，没有错误。逐行运行时，我看到该函数总是返回 false，这会提示下一行并最终提示代码的结尾，而不会进行任何实际更改(为了简洁起见，我省略了)

OBS2：有一些变量已声明但未使用。它们用于代码另一部分中的计算。这就是为什么我从顶部删除了"显式选项"的原因。

Answer 1

你为什么不试试下面的函数？

Sub Test()
Dim arr As Variant
arr = Split("abc,def,ghi,jkl", ",")
Debug.Print IsInArray("ghi", arr)
End Sub

看起来您的函数无法正常工作。 如何在数组中搜索字符串

Answer 2

你可以使用类似的东西来代替你的"IsInArray"函数：

'...
'If IsInArray(i_number, ilist) = True Then
Set cell = wb.Sheets("B").Range(Cells(1, 2), Cells(lastcol, 1)).Find(What:=i_number, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not(cell Is Nothing) Then
ifinalmatch = Application.HLookup(i_number, wb.Sheets("A").UsedRange, lastRow, False) 'column
End If
'...