没有仔细测试 [code=VBA] Public Function mylookup(lookup_value As String, table_array As Range, col_index_num As Integer, range_lookup As Boolean) Dim lngNum As Long Dim lngRow As Long Dim intCol As Integer lngRow = table_array.Rows.Count intCol = table_array.Columns.Count If range_lookup = False Then For lngNum = 1 To lngRow If table_array.Cells(lngNum, 1) = lookup_value Then mylookup = table_array.Cells(lngNum, 1).Offset(0, col_index_num - 1) Exit Function End If Next mylookup = CVErr(xlErrValue) Else For lngNum = 1 To lngRow If table_array.Cells(lngNum, 1) > lookup_value Then mylookup = table_array.Cells(lngNum - 1, 1).Offset(0, col_index_num - 1) Exit Function End If Next End If End Function [/code]
替代VLOOKUP的话,有点象SQL语句的: selcet TAB2.F1, TAB2.F2, TAB.F3 WHERE TAB2.F1 = TAB1.F1 纯用VB来实现,也就是从表1读一个数据就到表2中查找一次,效率比SQL低得多
其实vlookup很好用啊。
[code=VBA]
Public Function mylookup(lookup_value As String, table_array As Range, col_index_num As Integer, range_lookup As Boolean)
Dim lngNum As Long
Dim lngRow As Long
Dim intCol As Integer
lngRow = table_array.Rows.Count
intCol = table_array.Columns.Count
If range_lookup = False Then
For lngNum = 1 To lngRow
If table_array.Cells(lngNum, 1) = lookup_value Then
mylookup = table_array.Cells(lngNum, 1).Offset(0, col_index_num - 1)
Exit Function
End If
Next
mylookup = CVErr(xlErrValue)
Else
For lngNum = 1 To lngRow
If table_array.Cells(lngNum, 1) > lookup_value Then
mylookup = table_array.Cells(lngNum - 1, 1).Offset(0, col_index_num - 1)
Exit Function
End If
Next
End If
End Function
[/code]
selcet TAB2.F1, TAB2.F2, TAB.F3 WHERE TAB2.F1 = TAB1.F1
纯用VB来实现,也就是从表1读一个数据就到表2中查找一次,效率比SQL低得多