VBA 两个查找条件的Vlookup 组内序号产生 两个参数的Vlookup

常用的3個VBA自定義函數

组内序号产生

‘兩個查找條件的Vlookup
Function TQ_MultiVLookup(FindChar1 As String, FindChar2 As String, FindArea)
   Dim n As Integer
   n = FindArea.Columns.Count
   For i = 1 To FindArea.Rows.Count
       If FindChar1 = FindArea.Cells(i, 1) And FindChar2 = FindArea.Cells(i, 2) Then
          TQ_MultiVLookup = FindArea.Cells(i, n)
          Exit For
       End If
   Next i
End Function
 
‘組內序號產生
Function TQ_MakeSequence(s As Range)
   If s.Cells(0, 1) <> s.Cells(1, 1) Then
      TQ_MakeSequence = 1
   Else
      TQ_MakeSequence = Application.ThisCell(0, 1) + 1
   End If
End Function

‘兩個參數的Vlookup，列數與精確查找不用指定
Function TQ_VLookup(FindChar As String, FindArea)
   Dim n As Integer
   n = FindArea.Columns.Count
   For i = 1 To FindArea.Rows.Count
       If FindChar = FindArea.Cells(i, 1) Then
          TQ_VLookup = FindArea.Cells(i, n)
          Exit For
       End If
   Next i
End Function