自动填充未知数量的细胞

问题描述：

我正在尝试编写等效于单击的VBA代码，并将单元格的一角拖到最后.插图:

I'm trying to write VBA code for the equivalent of click and drag the corner of cell to the end. illustration:

我尝试了一些在网上找到的不同建议，但总是遇到错误.

I've tried several different suggestions I've found online but I am always met with errors.

下面是我当前正在处理的行.但是我收到运行时错误'1004':应用程序定义的错误或对象定义的错误.

Below is the line I am currently working on. But I am getting Run-time error '1004': Application-defined or object-defined error.

Range("A4").Select
ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlRight).Offset(0, -1))

有关更多上下文，这里是我正在研究的整个脚本:整个脚本的屏幕截图:
，这是我开始在线查找答案之前根据我自己的逻辑编写的原始脚本:

For more context here is the whole script I am working on: screenshot of whole script:
and here is the original line of script according to my own logic before I started looking for answers online:

Selection.Autofill Destination:=Range("A4":mylastcell_4), Type:=xlFillDefault

mylastcell_4已在脚本顶部声明(请参见整个脚本的屏幕截图).我也尝试过更改为"A4",mylastcell_4，但仍然收到错误.

mylastcell_4 has been declared at the top of the script (see screenshot of whole script). I've also tried changing to "A4",mylastcell_4 but I still receive the error.

我对VBA极为陌生，不知道从这里去哪里.

I am extremely new to VBA and have no idea where to go from here.

我尝试过

 .Range(.Cells(4, "A"), .Cells(1, .Columns.Count).End(xlToLeft).Offset(3, 0)).Formula = "=concatenate(a2, a1, a3)"

在以下答案之一中提出了建议.但是，只有在第1行中有5个单元格时，这才给出结果.我需要更大的灵活性，因为可能有2个值到500个值之间的任何地方都可以使用. 上述建议脚本结果的图片

Which is suggested in one of the answers below. However this only gives the result if I have 5 cells in row 1. I require more flexibility as there might be anywhere from 2 values to 500 values to work with. Image of results of above suggested script

我还发现，沿着这条路线走似乎会破坏我所知道的单独工作的下一个串联-希望它简单一些需要改变吗? 用于下一个串联的脚本.

I've also found that going along this route seems to destroy my next lot of concatenation that I know worked well separately - hopefully its something simple that needs altering? Script used for next concatenation.

此练习的最终目标是获取准备直接用于SQL的文本列表.即a b c d e都在一个单元格中全部变为'a'，'b'，'c'，'d'，'e'.

The ultimate goal of this exercise is to get a list of text prepared to go straight into SQL. i.e. a b c d e becomes 'a','b','c','d','e' all in one cell.

答

您要添加的内容是放在A4中的此公式.

The content you want to add is this formula placed in A4.

=concatenate(a2, a1, a3)

由于前面的所有三行对公式都很重要，因此可以推断出，前面的任何一行都可以用于右侧的停止"点.我将使用第1行(上方3行)并向下偏移以定位停止"点.

Since all three preceding rows are important to the formula, it's inferred that any of the preceding rows could be used for a 'stop' point to the right. I'll use row 1 (3 rows above) and offset down to locate the 'stop' point.

with worksheets("sheet1")
    ...
    .cells(4, "A").formula = "=concatenate(a2, a1, a3)"
    .range(.cells(4, "A"), .cells(1, .columns.count).end(xltoleft).offset(3, 0)).fillright
    ...
end with

与.AutoFill相比，我更喜欢使用.FillRight进行此操作，但是您也可以轻松地对.AutoFill使用此方法.

I prefer .FillRight for this operation over .AutoFill but you can easily use this method for .AutoFill as well.

或者，您可以一次将公式写到所有四个单元格中.

Alternately, you could have just written the formula into all four cells at once.

with worksheets("sheet1")
    ...
    .range(.cells(4, "A"), .cells(1, .columns.count).end(xltoleft).offset(3, 0)).formula = "=concatenate(a2, a1, a3)"
    ...
end with

您需要在每个范围和单元格前面加上.(例如.Range(...)和.Cells(...))，以使with worksheets("sheet1")正常工作.

You need to preface each Range and Cells with . like .Range(...) and .Cells(...) in order for your with worksheets("sheet1") to work properly.

在您自己的版本中，如果正确设置了 mylastcell_4 ，则可能可以解决此问题.

In your own version, if mylastcell_4 was set correctly, then this would likely have solved the issue.

.Range("A4").Autofill Destination:=.Range("A4:" & mylastcell_4.address), Type:=xlFillDefault

您设置了一个With Worksheet ... End With块，然后再也没有使用它.

You set up a With Worksheet ... End With block then never appeared to use it.

自动填充未知数量的细胞

相关推荐