运行出错，找不到方法？

hhl1984

怎么会出现这种错误，一出错就要发送出错报告！

我心飞翔410

你这个 能不能传个附件
可能是range引用的单元格区域超出表格的规定，比如你在03版里引用了第65537行或257列，这都会导致引用失败。  

hhl1984

Sub nrb() '单据号自动编码
Dim mydata As String, mytable As String, sql As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ii As Integer
Dim myarray As Variant
Dim rng As Range
mydata = ThisWorkbook.Path & "\MCL.mdb"
mytable = "ruku"
Set rng = Range("d2")
Set cnn = New ADODB.Connection
With cnn
    .Provider = "microsoft.jet.oledb.4.0"
    .Open mydata
End With
sql = "select distinct  文件号 from ruku "
Set rs = New ADODB.Recordset
rs.Open sql, cnn, 3, adLockOptimistic
If rs.RecordCount <> 0 Then
rs.MoveFirst
    Do While rs.EOF = False
        trs = rs.Fields("文件号")
        dt = Mid(trs, 7, 8)
        If dt = Format(Date, "yyyymmdd") Then
            i = Val(Right(trs, Len(trs) - InStr(trs, "-")))
            rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(i + 1, "00") '会出现
        Else
            rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(1, "00") '会出现
        End If
        rs.MoveNext
    Loop
Else
    rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(1, "00")
End If
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn
end sub
会出现有备注出来，帮忙看一下，是不是代码的问题。附件太大了！

那么的帅

那句代码出现错误提示

hhl1984

Sub nrb() '单据号自动编码
Dim mydata As String, mytable As String, sql As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ii As Integer
Dim myarray As Variant
Dim rng As Range
mydata = ThisWorkbook.Path & "\MCL.mdb"
mytable = "ruku"
Set rng = Range("d2")
Set cnn = New ADODB.Connection
With cnn
    .Provider = "microsoft.jet.oledb.4.0"
    .Open mydata
End With
sql = "select distinct  文件号 from ruku "
Set rs = New ADODB.Recordset
rs.Open sql, cnn, 3, adLockOptimistic
If rs.RecordCount <> 0 Then
rs.MoveFirst
    Do While rs.EOF = False
        trs = rs.Fields("文件号")
        dt = Mid(trs, 7, 8)
        If dt = Format(Date, "yyyymmdd") Then
            i = Val(Right(trs, Len(trs) - InStr(trs, "-")))
            rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(i + 1, "00") '会出现        Else
            rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(1, "00") '会出现        End If
        rs.MoveNext
    Loop
Else
    rng.Value = "XMIN R" & Format(Date, "yyyymmdd") & "-" & Format(1, "00")End If
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn
end sub
红色字体会出现错误提示，就是有用到VALUE都会提示！

那么的帅

hhl1984 发表于 2013-10-14 16:02
Sub nrb() '单据号自动编码
Dim mydata As String, mytable As String, sql As String
Dim cnn As ADODB. ...

Set rng = Range("d2")
前面加上工作表名

如  Set rng = Sheets("Sheet1").Range("d2")

hhl1984

还是不行.我现在发现一个问题。
如果我取值，比如RANGE("D2").value="XMIH R20131014-01"运行就不会错；但是我要是用变量就会出错，range("d2").value=v1。

fengai99

来学习了~