单元溢出行值

Question

Sub InsertRow()
Call BlankColumns
'Call DeleteZeros
'normalizes the data extracted from QM QSRs
'If Columns included in QSR Change, THe column references will have to be adjusted
Dim lastcol As Integer 'Idenfies How many Questions are in Dataset
Dim r2a As Long 'Row to copy
Dim nr As Integer '# of people to copy
Dim r2s As Integer 'will copy each data set for however many questions there is
Dim R As Integer 'Counts how many people are in the dataset

R = Range("A4", Range("A4").End(xlDown)).Rows.Count

    With ActiveSheet
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column - 6
         r2a = (lastcol / 2) - 2
         r2s = (lastcol / 2)
             End With
    
For nr = 0 To R
     Cells(((nr * r2s) + 4), 1).EntireRow.Copy
    
         Range(ActiveCell, ActiveCell.Offset((r2a), 0)).EntireRow.Insert Shift:=xlDown
            Next nr

Call pasteanswers
Call PasteActivityCode

Question = MsgBox("Upload information to Database?", vbYesNo + vbQuestion, "Database Upload")

If Question = vbYes Then
Call SaveWorkbook
Call ExportData

Else

End If


End Sub

错误发生在

Cells(((nr * r2s) + 4), 1).EntireRow.Copy

​

在上面的代码中，当单元格行值大于32,768时，我遇到了问题，如何使它允许长时间而不是整数

我不知道如何定义这个单元格以允许它引用大于整数的行。替代解决方案也是非常感谢的！

Answer 1

因为Integer '#将Dim r2s复制为Integer‘将复制每个数据集，但是有许多问题存在Dim，因为Integer’计算数据集中有多少人

改为：

Dim nr As Long
Dim r2s As Long
Dim R As Long

每当您处理工作表行或任何不符合16位整数(Integer)最大值的值时，都需要将变量声明为Long，因为正如您注意到的，32,768溢出了一个Integer。

尽管如此，我强烈建议您将这些变量重命名为使用有意义的标识符，这样就不需要注释来告诉您它们代表什么了。

Dim lastColumn As Long
Dim rowToCopy As Long
Dim nbPeopleToCopy As Long
Dim r2s As Long 'sorry, not clear from comment or usage
Dim nbRows As Long