VBA用三种方法实现类似vlookup函数功能

VBA用三种方法实现类似vlookup函数功能http://pan.baidu.com/s/1qXuHT1a

2025-02-26 04:25:04

推荐回答（3个）

回答1：

Public Function VLOOKUP1(ByVal lookup_value As String, ByVal table_array As Range, ByVal col_index_num As Integer) As String
Dim i As Long

For i = 1 To table_array.Rows.Count
If lookup_value = table_array.Cells(table_array.Row + i - 1, 1) Then
VLOOKUP1 = table_array.Cells(table_array.Row + i - 1, col_index_num)
Exit For
End If
Next i

End Function

回答2：

Sub s()
    arr = [a7:e26]
    Set d = CreateObject("scripting.dictionary")
    For i = 2 To 5
        Set d(arr(1, i)) = CreateObject("scripting.dictionary")
        For j = 2 To 20
            d(arr(1, i))(arr(j, 1)) = arr(j, i)
        Next
    Next
    For i = 20 To 23
        For j = 8 To 12
            Cells(j, i) = d(Cells(7, i).Text)(Cells(j, 19).Text)
        Next
    Next
End Sub

回答3：

Sub pipei()

Dim i, j

i = 5
j = 5

Do While i < 14 Or j < 14

If Cells(j, 2) = Cells(i, 5) Then

Cells(j, 3) = Cells(i, 6)
j = j + 1
i = 5

Else: i = i + 1

End If

If i > 13 Then

j = j + 1
i = 5

End If

If j > 13 Then

Exit Do

End If

Loop

End Sub