excel - Application.inputbox 在范围选择上抛出错误 424

Question

下面是我正在处理的宏的一部分，它试图将分页的工作簿数据转换为表格格式。我编写了错误处理程序以在“取消”选择时退出子程序，但由于某种原因，当我指向一个范围并选择“确定”时，它也会引发 424 运行时错误。但奇怪的是，它只有在指定第三组数据的位置后才会出错。

VBA 对单个宏中可以拥有的输入框数量有限制吗？

multicol: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'gather variables for second set of prekit shipping data
Set rng = Nothing
Set rng = Application.InputBox("Point to second store number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topStore1 = rng
Set botStore1 = rng.End(xlDown)

psf.Worksheets(topStore1.Worksheet.Name).Activate
ActiveSheet.Range("A1").Select
Set rng = Application.InputBox("Point to second prekit number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topPrek1 = rng
Set botPrek1 = rng.End(xlDown)

Set rng = Application.InputBox("Point to second kit qty number. If none exists hit cancel.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then
        qty1 = 1
    Else
        qty1 = 0
        Set topQty1 = rng
        Set botQty1 = rng.End(xlDown)
    End If

meythod1 = Application.InputBox("How are these kits being shipped? `JOLIET` `FEDEX` `VDN`", "Shipping Method", Type:=2)
    
answer = MsgBox("Is there another set of shipping information?", vbYesNo + vbQuestion, "Multiple Prekits")
    If answer = vbNo Then
        multy = 1
        GoTo singlecol
    End If

'gather variables for third set of prekit shipping data
Set rng = Nothing
Set rng = Application.InputBox("Point to third store number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topStore2 = rng
Set botStore2 = rng.End(xlDown)

psf.Worksheets(topStore2.Worksheet.Name).Activate
ActiveSheet.Range("A1").Select
Set rng = Application.InputBox("Point to third prekit number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topPrek2 = rng
Set botPrek2 = rng.End(xlDown)

Set rng = Application.InputBox("Point to third kit qty number. If none exists hit cancel.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then
        qty2 = 1
    Else
        qty2 = 0
        Set topQty2 = rng
        Set botQty2 = rng.End(xlDown)
    End If

meythod2 = Application.InputBox("How are these kits being shipped? `JOLIET` `FEDEX` `VDN`", "Shipping Method", Type:=2)
    
answer = MsgBox("Is there another set of shipping information?", vbYesNo + vbQuestion, "Multiple Prekits")
    If answer = vbNo Then
        multy = 2
        GoTo singlecol
    End If

'gather variables for fourth set of prekit shipping data
Set rng = Nothing
Set rng = Application.InputBox("Point to fourth store number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topStore3 = rng
Set botStore3 = rng.End(xlDown)

psf.Worksheets(topStore3.Worksheet.Name).Activate
ActiveSheet.Range("A1").Select
Set rng = Application.InputBox("Point to fourth prekit number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topPrek3 = rng
Set botPrek3 = rng.End(xlDown)

Set rng = Application.InputBox("Point to fourth kit qty number. If none exists hit cancel.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then
        qty3 = 1
    Else
        qty3 = 0
        Set topQty3 = rng
        Set botQty3 = rng.End(xlDown)
    End If

meythod3 = Application.InputBox("How are these kits being shipped? `JOLIET` `FEDEX` `VDN`", "Shipping Method", Type:=2)
    
answer = MsgBox("Is there another set of shipping information?", vbYesNo + vbQuestion, "Multiple Prekits")
    If answer = vbNo Then
        multy = 3
        GoTo singlecol
    End If

'gather variables for fifth set of prekit shipping data
Set rng = Nothing
Set rng = Application.InputBox("Point to fifth store number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topStore4 = rng
Set botStore4 = rng.End(xlDown)

psf.Worksheets(topStore4.Worksheet.Name).Activate
ActiveSheet.Range("A1").Select
Set rng = Application.InputBox("Point to fifth prekit number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topPrek4 = rng
Set botPrek4 = rng.End(xlDown)

Set rng = Application.InputBox("Point to fifth kit qty number. If none exists hit cancel.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then
        qty4 = 1
    Else
        qty4 = 0
        Set topQty4 = rng
        Set botQty4 = rng.End(xlDown)
    End If

meythod4 = Application.InputBox("How are these kits being shipped? `JOLIET` `FEDEX` `VDN`", "Shipping Method", Type:=2)
    
answer = MsgBox("Is there another set of shipping information?", vbYesNo + vbQuestion, "Multiple Prekits")
    If answer = vbNo Then
        multy = 4
        GoTo singlecol
    End If
    
'gather variables for sixth set of prekit shipping data
Set rng = Nothing
Set rng = Application.InputBox("Point to sixth store number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topStore5 = rng
Set botStore5 = rng.End(xlDown)

psf.Worksheets(topStore5.Worksheet.Name).Activate
ActiveSheet.Range("A1").Select
Set rng = Application.InputBox("Point to sixth prekit number.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then Exit Sub
    If Err.Number <> 0 And Err.Number <> 424 Then Debug.Print Err.Number
Set topPrek5 = rng
Set botPrek5 = rng.End(xlDown)

Set rng = Application.InputBox("Point to sixth kit qty number. If none exists hit cancel.", "Obtain Range Object", Type:=8)
    If Err.Number = 424 Then
        qty5 = 1
    Else
        qty5 = 0
        Set topQty5 = rng
        Set botQty5 = rng.End(xlDown)
    End If

meythod5 = Application.InputBox("How are these kits being shipped? `JOLIET` `FEDEX` `VDN`", "Shipping Method", Type:=2)
    
answer = MsgBox("Is there another set of shipping information?", vbYesNo + vbQuestion, "Multiple Prekits")
    If answer = vbNo Then
        multy = 5
        GoTo singlecol
    End If

标签： excelvba