将细胞中的换行符添加到1000行数据 [英] Add line breaks in cell to 1000 rows of data

问题描述

我正在使用下面的代码在列C中的单元格数据中添加换行符并将其复制到列K.我需要对一系列数据应用换行符。我在列C中有1000行数据。
任何帮助将不胜感激。

I am currently using the below code to add a line break to cell data in column C and copy it to column K. I need to apply line breaks to a range of data. I have 1000 rows of data in column C. Any help will be much appreciated.

Sub Macro
    Dim Stem As Variant
    Stem = ThisWorkbook.Worksheets ("Sheet1").Range("C2")
    Range ("K2").Select
    Range("K2").FormulaR1C1 = Stem & Chr(10) & ""
End Sub

谢谢

推荐答案

一些事情：

• 更好地早日约束你的变数记忆
  管理，利用智能感知等）


• 通常最好的
  练习，以避免使用选择如果可能。


• 您的Stem变量是一个对象（范围对象），因此需要设置

尝试这样：

Sub Macro
    Dim WS As Worksheet
    Dim Stem As Range
    Dim R2 As Range
    Dim Rng as Range

    Set WS = ActiveWorkbook.Sheets("Sheet1")
    Set Stem = WS.Range("C2", Cells(WS.Range("C2").End(xlDown).Row, WS.Range("C2").Column))
    Set R2 = WS.Range("K2", Cells(Stem.End(xlDown).Row, WS.Range("K2").Column))

    R2.Value = Stem.Value
    'New Code
    For Each Rng In R2
        Rng.Value = Rng.Value & Chr(10) & ""
    Next Rng
    'Old Code: R2.End(xlDown) = R2.End(xlDown) & Chr(10) & ""

End Sub

这是做什么首先设置工作表你重新使用。然后，使用Range（cell1，cell2）格式设置工作范围（Stem）。 Cell1我定义为C2。下一个表达式是使用Cells（）函数。这是VBA相当于在C2并击中Ctl + Down，然后看到你在哪一行。

What this does is first sets the worksheet you're using. Then, you set your working range (Stem) using the Range(cell1, cell2) format. Cell1 I defined as "C2". The next expression there is using the Cells() function. It is the VBA equivalent of being in "C2" and hitting Ctl+Down, then seeing what row you're in.

然后，我设置你的目的地范围，R2以类似的方式，但是我使用了阀杆范围来确定应该有多大。

Then, I set your destination range, R2, in a similar manner, but I used the Stem range to determine how large it should be.

最后，为了获得准确的复印，您的目标范围必须相同作为你的范围。 .value表达式的.value粘贴数据。然后，您的额外的字符将添加到您的新数据字段。

Finally, to get an accurate copy your destination range must be the same size as your from range. The .value to .value expression pastes the data. Then, your extra characters are added on to your new data field.

要注意的事项.End（xlDown）...如果在你的数据中间会停在那里，而不是一直到最后。希望这有帮助！

Something to keep in mind with .End(xlDown)... if you have blank rows in the middle of your data it will stop there, not go all the way down to the end. Hope this helps!

编辑：
For Each 遍历目标范围R2中的每个范围（即单元格），并添加新的字符。希望适合你。

The For Each loop will go through every range (i.e. cell) in your destination range, R2, and add your new characters. Hope that fits better for you.

这篇关于将细胞中的换行符添加到1000行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！