weixin_38225865
weixin_38225865
采纳率100%
2021-03-08 14:28

求大神相助,一小段vba调试不通

100
已采纳

这段程序要实现的是:
把“电话回访记录_临时”表中的“退单”“发门锁单”所在行的一些信息,
写入“电话回访记录_所有”表中。
一直调试不通,报错:运行时错误‘9’,下标越界

Sub TuiDaMenSuoDan()
    Dim dateFaShengRiQi As Date
    Dim strYongHuDiZhi As String
    Dim strYongHuDianHua As String
    Dim strZhuangTai As String
    Dim strYuanGong As String
    Dim strFanYingRen As String
    Dim i As Integer
    Dim strGongZuoBiaoMing As String
    Dim wb As Workbook
    Dim intMuBiaoBiaoZuiHouYiHang As Integer
    Dim intGongZuoBiaoChangDu As Integer
'**********************************************
    Workbooks.Open Filename:="d:\Documents\电话回访记录_20210305\电话回访记录_所有.xlsx"
    strGongZuoBiaoMing = InputBox("请输入工作表名:")
    intGongZuoBiaoChangDu = Worksheets(strGongZuoBiaoMing).Range("A65536").End(xlUp).Row
    For i = 1 To intGongZuoBiaoChangDu
        If Trim(Sheets(strGongZuoBiaoMing).Cells(i, "C")) = "退单" Then
            dateFaShengRiQi = Sheets(strGongZuoBiaoMing).Cells(i, "D")
            strYongHuDiZhi = Sheets(strGongZuoBiaoMing).Cells(i, "I")
            strYongHuDianHua = Sheets(strGongZuoBiaoMing).Cells(i, "J")
            strZhuangTai = Sheets(strGongZuoBiaoMing).Cells(i, "C")
            If Sheets(strGongZuoBiaoMing).Cells(i, "N") = "" Then
                strYuanGong = ""
            Else
                strYuanGong = Sheets(strGongZuoBiaoMing).Cells(i, "N")
            End If
            strFanYingRen = Sheets(strGongZuoBiaoMing).Cells(i, "H")
            MsgBox (strFanYingRen)
'***********************************************
            intMuBiaoBiaoZuiHouYiHang = Worksheets(Trim("2021-03")).Range("A65536").End(xlUp).Row
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "A") = dateFaShengRiQi
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "B") = strYongHuDiZhi
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "C") = strYongHuDianHua
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "D") = strZhuangTai
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "E") = strYuanGong
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "H") = strFanYingRen
        End If
    Next i
'**********************************************
    For i = 1 To intGongZuoBiaoChangDu
        If Trim(Sheets(strGongZuoBiaoMing).Cells(i, "L")) = "发门锁单" Then
            dateFaShengRiQi = Sheets(strGongZuoBiaoMing).Cells(i, "D")
            strYongHuDiZhi = Sheets(strGongZuoBiaoMing).Cells(i, "I")
            strYongHuDianHua = Sheets(strGongZuoBiaoMing).Cells(i, "J")
            strZhuangTai = Sheets(strGongZuoBiaoMing).Cells(i, "L")
            If Sheets(strGongZuoBiaoMing).Cells(i, "N") = "" Then
                strYuanGong = ""
            Else
                strYuanGong = Sheets(strGongZuoBiaoMing).Cells(i, "N")
            End If
            strFanYingRen = Sheets(strGongZuoBiaoMing).Cells(i, "H")
            MsgBox (strFanYingRen)
'***********************************************
            intMuBiaoBiaoZuiHouYiHang = Worksheets(Trim("2021-03")).Range("A65536").End(xlUp).Row
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "A") = dateFaShengRiQi
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "B") = strYongHuDiZhi
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "C") = strYongHuDianHua
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "D") = strZhuangTai
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "E") = strYuanGong
            Sheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "H") = strFanYingRen
        End If
    Next i
End Sub
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

7条回答

  • showbo GoCityPass新加坡曼谷通票 1月前

    你确认你的Excel文件路径正确?

    还有工作表名称也是对的?

    如果这2个有一个出错都会提示越界的错误。

     

        strGongZuoBiaoMing = Trim(InputBox("请输入工作表名:")) 
        '增加下面的语句看下表名称都有什么,是不是全数字由于Excel版本不一样导致名称变了
           For Each s In ActiveWorkbook.Sheets
             MsgBox "|" & s.Name & "|"
          Next

    不知道你数据格式怎么样的,按照你的代码瞎编的数据测试没有问题。

    o(╥﹏╥)o话说我安装了2016测试做表明名称全数字也是没问题。。。

     

    点赞 2 评论 复制链接分享
  • soar3033 soar3033 1月前
    Sub TuiDaMenSuoDan()
    
    
        Dim dateFaShengRiQi As Date
    
    
        Dim strYongHuDiZhi As String
    
    
        Dim strYongHuDianHua As String
    
    
        Dim strZhuangTai As String
    
    
        Dim strYuanGong As String
    
    
        Dim strFanYingRen As String
    
    
        Dim i As Integer
    
    
        Dim strGongZuoBiaoMing As String
    
    
        Dim wb As Workbook
    
    
        Dim intMuBiaoBiaoZuiHouYiHang As Integer
    
    
        Dim intGongZuoBiaoChangDu As Integer
    
    
    '**********************************************
    
    
        Workbooks.Open Filename:="d:\Documents\电话回访记录_20210305\电话回访记录_所有.xlsx"
    
    
        strGongZuoBiaoMing = InputBox("请输入工作表名:")
    
    
        intGongZuoBiaoChangDu = Worksheets(strGongZuoBiaoMing).Range("A65536").End(xlUp).Row
    
    
        For i = 1 To intGongZuoBiaoChangDu
    
    
            If Trim(Worksheets(strGongZuoBiaoMing).Cells(i, "C")) = "退单" Then
    
    
                dateFaShengRiQi = Worksheets(strGongZuoBiaoMing).Cells(i, "D")
    
    
                strYongHuDiZhi = Worksheets(strGongZuoBiaoMing).Cells(i, "I")
    
    
                strYongHuDianHua = Worksheets(strGongZuoBiaoMing).Cells(i, "J")
    
    
                strZhuangTai = Worksheets(strGongZuoBiaoMing).Cells(i, "C")
    
    
                If Worksheets(strGongZuoBiaoMing).Cells(i, "N") = "" Then
    
    
                    strYuanGong = ""
    
    
                Else
    
    
                    strYuanGong = Worksheets(strGongZuoBiaoMing).Cells(i, "N")
    
    
                End If
    
    
                strFanYingRen = Worksheets(strGongZuoBiaoMing).Cells(i, "H")
    
    
                MsgBox (strFanYingRen)
    
    
    '***********************************************
    
    
                intMuBiaoBiaoZuiHouYiHang = Worksheets(Trim("2021-03")).Range("A65536").End(xlUp).Row
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "A") = dateFaShengRiQi
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "B") = strYongHuDiZhi
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "C") = strYongHuDianHua
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "D") = strZhuangTai
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "E") = strYuanGong
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "H") = strFanYingRen
    
    
            End If
    
    
        Next i
    
    
    '**********************************************
    
    
        For i = 1 To intGongZuoBiaoChangDu
    
    
            If Trim(Worksheets(strGongZuoBiaoMing).Cells(i, "L")) = "发门锁单" Then
    
    
                dateFaShengRiQi = Worksheets(strGongZuoBiaoMing).Cells(i, "D")
    
    
                strYongHuDiZhi = Worksheets(strGongZuoBiaoMing).Cells(i, "I")
    
    
                strYongHuDianHua = Worksheets(strGongZuoBiaoMing).Cells(i, "J")
    
    
                strZhuangTai = Worksheets(strGongZuoBiaoMing).Cells(i, "L")
    
    
                If Worksheets(strGongZuoBiaoMing).Cells(i, "N") = "" Then
    
    
                    strYuanGong = ""
    
    
                Else
    
    
                    strYuanGong = Worksheets(strGongZuoBiaoMing).Cells(i, "N")
    
    
                End If
    
    
                strFanYingRen = Worksheets(strGongZuoBiaoMing).Cells(i, "H")
    
    
                MsgBox (strFanYingRen)
    
    
    '***********************************************
    
    
                intMuBiaoBiaoZuiHouYiHang = Worksheets(Trim("2021-03")).Range("A65536").End(xlUp).Row
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "A") = dateFaShengRiQi
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "B") = strYongHuDiZhi
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "C") = strYongHuDianHua
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "D") = strZhuangTai
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "E") = strYuanGong
    
    
                Worksheets("2021-03").Cells(intMuBiaoBiaoZuiHouYiHang + 1, "H") = strFanYingRen
    
    
            End If
    
    
        Next i
    
    
    End Sub

    你把这段程序复制过去看看。

    点赞 评论 复制链接分享
  • bill20100829 歇歇 1月前

    这样写
        intGongZuoBiaoChangDu = Worksheets(strGongZuoBiaoMing).UsedRange.Rows.Count

    点赞 评论 复制链接分享
  • youyingbo tiger波波 1月前

    是在这一行报错,但是应该怎样写呢?我是vba新手,还望大神指点

    -------------------------

    “请输入工作表名”:这一步你输入的是什么?是不是输错了?或者不支持中文?你打开你的excel表格,看看你要操作的sheet页的名字是什么??

    比如我这个,输入Sheet1就没问题。

    点赞 评论 复制链接分享
  • soar3033 soar3033 1月前

     intGongZuoBiaoChangDu = Worksheets(strGongZuoBiaoMing).Range("A65536").End(xlUp).Row     xlUp 改成xlDown  !!!!!!

    下面的几个也要改!!!!

    点赞 1 评论 复制链接分享
  • youyingbo tiger波波 1月前

    能把表格提供下吗?信息是假的也行

    点赞 评论 复制链接分享
  • niushaolongniuniu niushaolongniuniu 1月前

    intGongZuoBiaoChangDu 可能获取错误了

    点赞 评论 复制链接分享