vba多条件查找供应商?
发布网友
发布时间：2023-05-08 17:27
共2个回答
热心网友
时间：2023-11-16 17:57
```VBA
Sub sort_suppliers()
Dim ws As Worksheet
Dim lRow As Long
Dim FoundCell As Range
Dim strSearch As String
Dim sortRange As Range
'设置需要操作的工作表
Set ws = ThisWorkbook.Sheets("Sheet1")
'获取最后一行
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
'设置查找参数，如果需要可以将其改动为输入框让用户输入。
Dim cond_GXDM As String
cond_GXDM = "111" '条件工序代码
Dim cond_CPMC As String
cond_CPMC = "产品AB" '类产品
Dim cond_GYSJB As String
cond_GYSJB = "级别A" '供应商级别
'在第一图查找符合条件的单元格，将查找范围*在前三列中
Set FoundCell = ws.Range("A2:A" & lRow).Find(what:=cond_GXDM)
If Not FoundCell Is Nothing Then
Set FoundCell = ws.Range("B" & FoundCell.Row & ":D" & lRow).Find(what:=cond_CPMC & "," & cond_GYSJB)
If Not FoundCell Is Nothing Then
'找到相应行之后，利用Offset函数获取该供应商的PPV值
Set sortRange = FoundCell.Offset(0, 1).Resize(1, 1)
'向下查找并选取符合条件的单元格，将其合并为一区域
Do Until IsEmpty(FoundCell) Or FoundCell.Offset(0, -1) <> cond_GXDM
If FoundCell.Offset(0, 1) = cond_CPMC And FoundCell.Offset(0, 2) = cond_GYSJB Then
Set sortRange = Application.Union(sortRange, FoundCell.Offset(0, 3).Resize(1, 1))
End If
Set FoundCell = ws.Range("B" & FoundCell.Row + 1 & ":D" & lRow).Find(what:=cond_CPMC & "," & cond_GYSJB)
Loop
'按照PPV值从小到大排序
sortRange.Sort key1:=sortRange, order1:=xlAscending, Header:=xlNo
Else
MsgBox "无符合条件的供应商。"
End If
Else
MsgBox "无符合条件的记录。"
End If
End Sub
```
此代码仅供参考，具体操作需要根据您的实际需求进行调整和修改。
热心网友
时间：2023-11-16 17:58
FILTER 根据条件筛选多条记录
SORT 对表格进行排序
两个函数，参考下
