时间戳

首页 > 解决方案 > VBA: if-else-statement 运行时错误'-2147417848 (80010108)'

excel - VBA: if-else-statement 运行时错误'-2147417848 (80010108)'

问题描述

我在开发数据输入表单时遇到了与 MS Excel VBA 相关的错误。

以下是我的数据输入表单/用户表单：-

用户表单

现在，我想做的是：-

1- 有 1x PI 号和所有左侧详细信息（类型、产品类型、子产品类型、产品代码、客户和品牌）

2- 使用每个输入的尺寸（订单和包装）复制所有左侧细节
*可以参考用户表单图片中红色突出显示的插图

在我完成以下代码之后： -

Dim sh As Worksheet
Dim iRow As Long
Dim j As Long

Set sh = ThisWorkbook.Sheets("PKG_Database")

iRow = [Counta(PKG_Database!A:A)] + 1

If PKGform.PKGText_prodcode.Value = "" Or PKGform.PKGText_prodcode.Value = 0 Then

MsgBox ("You did not enter the details")
 
Else
        
    If PKGform.PKGTxt_XSorder = "" Or PKGform.PKGTxt_XSorder = 0 Then
        
        Else
        With sh
        
        .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
        .Cells(iRow, 2) = PKGform.PKGText_PI.Value
        .Cells(iRow, 3) = PKGform.PKGText_prodtype
        .Cells(iRow, 4) = PKGform.PKGText_subprodtype
        .Cells(iRow, 5) = PKGform.PKGText_prodcode
        .Cells(iRow, 6) = PKGform.PKGText_customer
        .Cells(iRow, 7) = PKGform.PKGText_brand
        .Cells(iRow, 8) = "XS"
        .Cells(iRow, 9) = PKGform.PKGTxt_XSorder
        .Cells(iRow, 10) = PKGform.PKGTxt_XSpacked
        .Cells(iRow, 12) = Application.UserName
        iRow = iRow + 1
        
        End With
        End If
        
            
            If PKGform.PKGTxt_Sorder = "" Or PKGform.PKGTxt_Sorder = 0 Then
            
            Else
            With sh
            
            .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
            .Cells(iRow, 2) = PKGform.PKGText_PI.Value
            .Cells(iRow, 3) = PKGform.PKGText_prodtype
            .Cells(iRow, 4) = PKGform.PKGText_subprodtype
            .Cells(iRow, 5) = PKGform.PKGText_prodcode
            .Cells(iRow, 6) = PKGform.PKGText_customer
            .Cells(iRow, 7) = PKGform.PKGText_brand
            .Cells(iRow, 8) = "S"
            .Cells(iRow, 9) = PKGform.PKGTxt_Sorder
            .Cells(iRow, 10) = PKGform.PKGTxt_Spacked
            .Cells(iRow, 12) = Application.UserName
            iRow = iRow + 1
            
            End With
            End If
            
            
                If PKGform.PKGTxt_Morder = "" Or PKGform.PKGTxt_Morder = 0 Then
                
                Else
                With sh
                
                .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
                .Cells(iRow, 2) = PKGform.PKGText_PI.Value
                .Cells(iRow, 3) = PKGform.PKGText_prodtype
                .Cells(iRow, 4) = PKGform.PKGText_subprodtype
                .Cells(iRow, 5) = PKGform.PKGText_prodcode
                .Cells(iRow, 6) = PKGform.PKGText_customer
                .Cells(iRow, 7) = PKGform.PKGText_brand
                .Cells(iRow, 8) = "M"
                .Cells(iRow, 9) = PKGform.PKGTxt_Morder
                .Cells(iRow, 10) = PKGform.PKGTxt_Mpacked
                .Cells(iRow, 12) = Application.UserName
                iRow = iRow + 1
                
                End With
                End If
                
                        If PKGform.PKGTxt_Lorder = "" Or PKGform.PKGTxt_Lorder = 0 Then
                    
                        Else
                        With sh
                        
                        .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
                        .Cells(iRow, 2) = PKGform.PKGText_PI.Value
                        .Cells(iRow, 3) = PKGform.PKGText_prodtype
                        .Cells(iRow, 4) = PKGform.PKGText_subprodtype
                        .Cells(iRow, 5) = PKGform.PKGText_prodcode
                        .Cells(iRow, 6) = PKGform.PKGText_customer
                        .Cells(iRow, 7) = PKGform.PKGText_brand
                        .Cells(iRow, 8) = "L"
                        .Cells(iRow, 9) = PKGform.PKGTxt_Lorder
                        .Cells(iRow, 10) = PKGform.PKGTxt_Lpacked
                        .Cells(iRow, 12) = Application.UserName
                        iRow = iRow + 1
                        
                        End With
                        End If
                        
                            If PKGform.PKGTxt_XLorder = "" Or PKGform.PKGTxt_XLorder = 0 Then
                            
                            Else
                            With sh
                            
                            .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
                            .Cells(iRow, 2) = PKGform.PKGText_PI.Value
                            .Cells(iRow, 3) = PKGform.PKGText_prodtype
                            .Cells(iRow, 4) = PKGform.PKGText_subprodtype
                            .Cells(iRow, 5) = PKGform.PKGText_prodcode
                            .Cells(iRow, 6) = PKGform.PKGText_customer
                            .Cells(iRow, 7) = PKGform.PKGText_brand
                            .Cells(iRow, 8) = "XL"
                            .Cells(iRow, 9) = PKGform.PKGTxt_XLorder
                            .Cells(iRow, 10) = PKGform.PKGTxt_XLpacked
                            .Cells(iRow, 12) = Application.UserName
                            iRow = iRow + 1
                            
                            End With
                            End If
                            
                                If PKGform.PKGTxt_XXLorder = "" Or PKGform.PKGTxt_XXLorder = 0 Then
                
                                Else
                                With sh
                                
                                .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
                                .Cells(iRow, 2) = PKGform.PKGText_PI.Value
                                .Cells(iRow, 3) = PKGform.PKGText_prodtype
                                .Cells(iRow, 4) = PKGform.PKGText_subprodtype
                                .Cells(iRow, 5) = PKGform.PKGText_prodcode
                                .Cells(iRow, 6) = PKGform.PKGText_customer
                                .Cells(iRow, 7) = PKGform.PKGText_brand
                                .Cells(iRow, 8) = "XXL"
                                .Cells(iRow, 9) = PKGform.PKGTxt_XXLorder
                                .Cells(iRow, 10) = PKGform.PKGTxt_XXLpacked
                                .Cells(iRow, 12) = Application.UserName
                                iRow = iRow + 1
                                
                                End With
                                End If
                                
                                    If PKGform.PKGTxt_XXXLorder = "" Or PKGform.PKGTxt_XXXLorder = 0 Then
                
                                    Else
                                    With sh
                                    
                                    .Cells(iRow, 1) = [Text(Now(),"DD/MM/YYYY")]
                                    .Cells(iRow, 2) = PKGform.PKGText_PI.Value
                                    .Cells(iRow, 3) = PKGform.PKGText_prodtype
                                    .Cells(iRow, 4) = PKGform.PKGText_subprodtype
                                    .Cells(iRow, 5) = PKGform.PKGText_prodcode
                                    .Cells(iRow, 6) = PKGform.PKGText_customer
                                    .Cells(iRow, 7) = PKGform.PKGText_brand
                                    .Cells(iRow, 8) = "XXXL"
                                    .Cells(iRow, 9) = PKGform.PKGTxt_XXXLorder
                                    .Cells(iRow, 10) = PKGform.PKGTxt_XXXLpacked
                                    .Cells(iRow, 12) = Application.UserName
                                    iRow = iRow + 1
                                    
                                    End With
                                    End If
                    

End If

假设，我在用户表单中输入 XS 和 S 尺寸的数字（订单和包装），XS 的详细信息（早期代码）完美地记录在表中。但它出现了 S（后来的代码）的运行时错误：- 运行时错误'-2147417848（80010108）'

通常，此代码一次只能捕获 1 个尺寸。如果超过 2 个或更多大小，它将仅捕获代码中较早者的数据，然后在 if else 结束语句对于较早大小（例如 XS 大小）之后的下一个代码将出现运行时错误。

我真的不知道是什么导致了错误。

您可以从此链接获取文件：- Excel 文件

**请找到名为“Submit2”的宏以查看代码。名为“show_form”的宏来执行用户表单。

有人可以帮我解决这个问题吗？非常感谢你的帮助:)

标签： excelvbadata-entry

解决方案

注意：以下未经测试的代码

使用方括号评估内容的问题。它充其量是多余的，并且可能是这里错误的根源。请尝试以下操作。

.Cells(iRow, 1).Value = Format(Now(),"DD/MM/YYYY")

存储格式化日期的文本值，或

.Cells(iRow, 1).Value = Now()
.Cells(iRow, 1).NumberFormat = "mm/dd/yy"

存储日期并按照您想要的方式对其进行格式化。

推荐阅读