用于查找范围内单元格值的VBA函数

Sub FindValueInRange()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As String
    
    ' 设置搜索范围和工作表
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set searchRange = ws.Range("A1:A100")
    
    ' 要查找的值
    searchValue = "Excel"
    
    ' 使用Find方法查找值
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    
    ' 检查是否找到
    If Not foundCell Is Nothing Then
        MsgBox "找到值在: " & foundCell.Address
    Else
        MsgBox "未找到值。"
    End If
End Sub

Function GetValueByIndexMatch(sheetName As String, lookupColumn As String, resultColumn As String, lookupValue As String) As Variant
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim matchResult As Long
    
    Set ws = ThisWorkbook.Sheets(sheetName)
    lastRow = ws.Cells(ws.Rows.Count, lookupColumn).End(xlUp).Row
    
    matchResult = Application.Match(lookupValue, ws.Range(lookupColumn & "1:" & lookupColumn & lastRow), 0)
    
    If Not IsError(matchResult) Then
        GetValueByIndexMatch = ws.Cells(matchResult, resultColumn).Value
    Else
        GetValueByIndexMatch = CVErr(xlErrNA) ' 返回#N/A错误
    End If
End Function