VBA make code simplier

2019-09-07 00:44发布

Please look at my code . There are two arrays doing the same but the two dimension array I can't use with Application.match().(I work on selling report in which i have daily sale. Next I compare with my array to calculate profit)

'****************************
Dim products As Variant
Dim productsWithParams As Variant
Dim pos, buyPrice, profit
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

products = Array("MS-CHOPMAT-6", "MS-BOARDS-3", "DEM-CROC-34")
productsWithParams = Array(Array("MS-CHOPMAT-6", 10), Array("MS-BOARDS-3", 10), Array("DEM-CROC-34", 10))
For x = LastRow To 2 Step -1
  order_quantity = Range("$E$" & x).Value
  item_price = Range("$F$" & x).Value

  ' if value not found inside the array using the "MATCH" function
  pos = Application.Match(Range("$D$" & x).Value, products, 0)
  If IsError(pos) Then
    Range("$H$" & x).Value = "ERROR - " 
  Else ' successful "MATCH" inside the array
    Range("$H$" & x).Value = order_quantity * 3
    buyPrice = productsWithParams(pos - 1)(1)
    profit = (item_price - buyPrice) * order_quantity
    Range("$L$" & x).Value = pos
    Range("$M$" & x).Value = buyPrice
    Range("$N$" & x).Value = profit
  End If
Next

0条回答
登录 后发表回答