VBA范围255个字符限制 [英] VBA Range 255 Character Limit

问题描述

我正在尝试在Excel中创建一个简单的数据库，并遇到VBA 255个字符限制的错误.我已经创建了几个作为String的变量，以将正确的输入单元格映射到数据库工作表中的相应列.不幸的是，我正在使用大量变量，因此字符总数超过255:

I'm trying to create a simple database in Excel and am running into an error with the VBA 255 character limit. I've created a couple of variables as String to map the correct input cells to the corresponding columns in the database worksheet. Unfortunately I'm working with a large number of variables so the total character count is over 255:

 myCopy = "D3,D5,D7,A14,B14,C14,D14,E14,G14,H14,I14,J14,K14,L14,M14,N14,O14,P14,R14,S14,T14,R15,S15,T15,R16,S16,T16,R17,S17,T17,U14,V14,W14,X14,Y14,U15,V15,W15,X15,Y15,U16,V16,W16,X16,Y16,U17,V17,W17,X17,Y17,Z14,AA14,AB14,AC14,Z15,AA15,AB15,AC15,Z16,AA16,AB16,AC16,Z17,AA17,AB17,AC17,A1,a1,a1,A1,a1,a1,A1,A1,A1,A1,A1,A1,A1,A1"
myCopy2 = "D3,D5,D7,A18,B18,C18,D18,E18,G18,H18,I18,J18,K18,L18,M18,N18,O18,P18,R18,S18,T18,R19,S19,T19,R20,S20,T20,R21,S21,T21,U18,V18,W18,X18,Y18,U19,V19,W19,X19,Y19,U20,V20,W20,X20,Y20,U21,V21,W21,X21,Y21,Z18,AA18,AB18,AC18,Z19,AA19,AB19,AC19,Z20,AA20,AB20,AC20,Z21,AA21,AB21,AC21,A1,a1,a1,A1,a1,a1,A1,A1,A1,A1,A1,A1,A1,A1"

当我尝试提交数据时，在以下行收到运行时错误'1004':对象'_Worksheet'的方法'范围'失败"错误消息:

When I attempt to submit the data, I get a "Run-time-error '1004': Method 'Range' of object'_Worksheet' failed" error message at the following line:

With inputWks
    Set myRng = .Range(myCopy)
End With

对数据库进行编码，以便在提交完整的输入表单时，每个单元格都将输入到下一列中，并且每一列都是一个不同的变量(请参见下文).因此，我无法删除上面字符串中列出的任何单元格，否则将无法在正确的位置输入数据.

The database is coded so that when the completed input form is submitted, each cell will be entered into the next column, and each column is a different variable (see below). As such, I can't delete any of the cells listed in the string above or the data won't be entered in the right place.

oCol = 3
    For Each myCell In myRng.Cells
        historyWks.Cells(nextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell

有人知道如何绕过Range的255个字符限制吗?

Does anyone know how to bypass the 255 character limit of Range?

推荐答案

您可以尝试类似的方法...

You may try something like this...

myCopy = "D3,D5,D7,A14:E14,G14:P14,R14:T17,U14:Y17,Z14:AC17,A1"

With inputWks
    Set myRng = .Range(myCopy)
End With

For Each myCell In myRng
    historyWks.Cells(nextRow, oCol).Value = myCell.Value
    oCol = oCol + 1
Next myCell

这篇关于VBA范围255个字符限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！