时间戳

首页 > 解决方案 > 当我乘以 2 个单元格时出现值错误 - vba

vba - 当我乘以 2 个单元格时出现值错误 - vba

问题描述

我在处理最后一个案例时遇到了一个值错误（1 个价格和 1 个数量）

样本数据：

数量为 100.345/300.456 或 300.345 或 300.345/500.369/500.456

价格是 300/400/500 或 300 或 300/400

数量和价格的数据将始终是相同的数字，因此如果有 2 个数量，将有 2 个价格，3 个数量将与 3 个不同的价格配对

因此，只有 3 种可能的情况，1 数量和 1 价格，2 数量和 2 价格，3 数量和 3 价格

我的预期输出是 qty*price 转换为 $#,##0.00 的总和

Function qty_price(qty As string, price As string)

    Dim price_1 As Double
    Dim price_2 As Double
    Dim price_3 As Double

    Dim slash_pos As Integer
    Dim slash_pos2 As Integer

    Dim qty_1 As Double
    Dim qty_2 As Double
    Dim qty_3 As Double

    price_1 = Val(Left(price, 3))
    price_2 = Val(Mid(price, 5, 3))
    price_3 = Val(Right(price, 3))

    slash_pos = InStr(1, qty, "/")
    qty_1 = Val(Left(qty, slash_pos - 1))
    slash_pos2 = InStr(9, qty, "/")
    qty_3 = Val(Right(qty, Len(qty) - slash_pos2))


    If InStr(1, qty, "/") <> 0 Then
        ' 3 price and 3 qty
        If Len(price) = 11 Then
            qty_2 = Mid(qty, slash_pos + 1, slash_pos2 - slash_pos - 1)
            qty_price = qty_1 * price_1 + qty_3 * price_3 + Val(qty_2) * price_2
        ' 2 price and 2 quantity
        ElseIf Len(price) = 7 And Len(qty) < 16 Then
            qty_2 = Right(qty, Len(qty) - slash_pos)
            qty_price = qty_1 * price_1 + Val(qty_2) * price_3

        End If

    Else
        ' 1 price and 1 qty
        If Len(price) = 3 And Len(qty) < 8 And InStr(1, qty, "/") = 0 Then
            'value error over here
            **qty_price = Val(qty) * Val(price)**

        End If
    End If

    qty_price = Format(qty_price, "$#,##0.00")

End Function

标签： vbavalueerror

解决方案

这可以改进，但只是一个开始。

在 1 个价格和 1 个数量的情况下，您至少有两个问题需要处理：

1）你得到了一个price_3 = Val(Right$(price, 3))你不想要的价值；

2）qty_1 = Val(Left$(qty, slash_pos - 1))将失败，因为slash_pos将是零，你不能做Left(x, -1).

下面我测试 1 个价格和 1 个数量的情况，如果是这种情况，则执行乘法、格式化字符串和退出函数。否则，继续设置的值price_3（尽管我认为您仍然需要仔细检查这对于两个数量和价格的情况是否正确，因为程序继续。）

Option Explicit
Public Sub test()
    Debug.Print qty_price("300.345", "300")
    Debug.Print qty_price("100.345/300.456", "300/400")
    Debug.Print qty_price("300.345/500.369/500.456", "300/400/500")
End Sub

Public Function qty_price(ByVal qty As String, ByVal price As String) As String
    Dim price_1 As Double, price_2 As Double, price_3 As Double
    Dim slash_pos As Long, slash_pos2 As Long
    Dim qty_1 As Double, qty_2 As Double, qty_3 As Double

    price_1 = Val(Left$(price, 3))
    price_2 = Val(Mid$(price, 5, 3))
    slash_pos = InStr(1, qty, "/")
    slash_pos2 = InStr(9, qty, "/")

    If slash_pos = 0 And slash_pos2 = 0 Then
        qty_price = Val(qty) * Val(price)
        qty_price = Format$(qty_price, "$#,##0.00")
        Exit Function
    End If

    price_3 = Val(Right$(price, 3))
    qty_1 = Val(Left$(qty, slash_pos - 1))
    qty_3 = Val(Right$(qty, Len(qty) - slash_pos2))

    If InStr(1, qty, "/") > 0 Then
        If Len(price) = 11 Then
            qty_2 = Mid$(qty, slash_pos + 1, slash_pos2 - slash_pos - 1)
            qty_price = qty_1 * price_1 + qty_3 * price_3 + Val(qty_2) * price_2
        ElseIf Len(price) = 7 And Len(qty) < 16 Then
            qty_2 = Right$(qty, Len(qty) - slash_pos)
            qty_price = qty_1 * price_1 + Val(qty_2) * price_3
        End If
    Else
        If Len(price) = 3 And Len(qty) < 8 And InStr(1, qty, "/") = 0 Then
            qty_price = Val(qty) * Val(price)
        End If
    End If
    qty_price = Format$(qty_price, "$#,##0.00")
End Function

推荐阅读