只需尝试使用Excel VBA将相同的字符串粘贴到一系列单元格中

I need to paste the same string value into every row of an excel spreadsheet. I have defined the string within a loop, via my vba code:

我需要将相同的字符串值粘贴到Excel电子表格的每一行。我通过我的vba代码在循环中定义了字符串:

    StickName = Left(myfile, 9)
    ActiveCell.FormulaR1C1 = StickName

It seems so simple to fill each row with the same value and I could do this via looping, writing into each cell until I reach the bottom row of the data, but there must be a more elegant solution. Is there a way to name a range (which would be a single column of data, from row x to y, within my loop) and fill an entire range with the same string? Then re-define the range and the string value for each iteration of the loop? I have searched on fill range with value and other similar searches and scoured the answers, but I don't see anything that addresses what I'm trying to do. Thank you for your help!

用相同的值填充每一行似乎很简单,我可以通过循环,写入每个单元格直到我到达数据的底行,但必须有一个更优雅的解决方案。有没有办法命名一个范围(在我的循环中,从第x行到第y行的单列数据)并用相同的字符串填充整个范围?然后为循环的每次迭代重新定义范围和字符串值?我已经搜索了填充范围的值和其他类似的搜索并搜索了答案,但我没有看到任何解决我正在尝试做的事情。谢谢您的帮助!

3 个解决方案

#1

Define a range and then set the value of the range.

定义范围,然后设置范围的值。

Dim r As Range
Dim s As String
s = "Hello World"
Set r = Sheets(1).Range("A1:A15")
r.Value = s

#2

You don't need any loops. Say we want to fill rows 7 through11 with some value:

你不需要任何循环。假设我们想用一些值填充第7行到第11行:

Sub liroch()
   Dim StickName As String, myfile As String

   myfile = "abcdefghijklmnop"
   StickName = Left(myfile, 9)
   Rows("7:11").Value = StickName
End Sub

#3

You can declare only your variable StickName and use this code:

您只能声明您的变量StickName并使用此代码:

StickName = Left(myfile, 9)
Range(Cells(startRow, StartColumn),Cells(LastRow, LastColumn)) = StickName

#1