时间戳

首页 > 解决方案 > 带有依赖组合框的过滤器

excel - 带有依赖组合框的过滤器

问题描述

实际上，我正在 VBA 中创建库存管理宏但我遇到了一个问题

我有一个productmaster表，如下列

Sr. No  Product  Purchase Price     Sale Price  Weight

每当我选择相同的产品但在组合框中更改重量时，购买/销售率应相应更改

但是虽然我改变了产品重量，但它采用相同的速度

所以你能告诉我我能做些什么来解决同样的问题吗

以下是我完成的编码

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("product_Master")


If Me.com_prod.Value = "" Or Me.com_trantype = "" Then Me.txt_rate.Value = ""

   ''' to filter on the basis of weight
   sh.Range("A1:I32000").AutoFilter field:=5, Criteria1:=Me.com_weight.Value
   If Me.com_trantype.Value = "Sale" Then

       On Error Resume Next
       Me.txt_rate.Value = Application.WorksheetFunction.VLookup(Me.com_prod, 
       sh.Range("B:E"), 3, 0)

        On Error GoTo 0
   ElseIf Me.com_trantype.Value = "Purchase" Then
        On Error Resume Next
        Me.txt_rate.Value = Application.WorksheetFunction.VLookup(Me.com_prod, 
        sh.Range("B:E"), 2, 0)
        On Error Resume Next
   End If

标签： excelvba

解决方案

尝试这个：

   Dim sh As Worksheet, col As Long, res

   Set sh = ThisWorkbook.Sheets("product_Master")

   If Me.com_prod.Value = "" Or Me.com_trantype = "" Then 
       Me.txt_rate.Value = ""
   Else
       '???
   End If 

   ''' to filter on the basis of weight
   sh.Range("A1:I32000").AutoFilter field:=5, Criteria1:=Me.com_weight.Value

   Select Case Me.com_trantype.Value
       Case "Sale": col = 3
       Case "Purchase": col = 2
   End Select

   If col > 0 Then
       'no need for error trapping if you drop WorksheetFunction...
       '  you can test the return value instead
       res = Application.VLookup(Me.com_prod, sh.Range("B:E"), col, 0)
       Me.txt_rate.Value = iif(iserror(res), "???", res)
   End If

推荐阅读