當前位置:成語大全網 - 新華字典 - vba多條件查找供應商?

vba多條件查找供應商?

以下是壹個可能的例子代碼,其中假設條件工序代碼在第1列,類產品在第2列,供應商級別在第3列,PPV值在第4列,並且數據的起始行為第2行。您可以根據您的實際情況進行相應的調整和修改。

```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

```

此代碼僅供參考,具體操作需要根據您的實際需求進行調整和修改。