vba Excel VBA宏根据列中的文本将单元格值复制并粘贴到某些单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8385803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Macros to copy and paste cell value to certain cells based on text in column
提问by Dane
I am attempting to copy and paste text values to a certain amount of cells in excel based on a text comparison.
我试图根据文本比较将文本值复制并粘贴到 excel 中的一定数量的单元格中。
Please see below for explanation:
请参阅以下说明：
I have 2 column A(blank) and B(with values)
我有 2 列 A（空白）和 B（带值）
After the macros, this will be the end results:
在宏之后，这将是最终结果：
It is basically taking the bottom cell after it detect the fruits column and pasting the cell value into row A. Note that after it reaches the next cell fruits it take the next (bottom) cell value. F123,F124 etc..
它基本上是在检测到水果列并将单元格值粘贴到行 A 后获取底部单元格。请注意，在到达下一个单元格水果后，它将获取下一个（底部）单元格值。F123、F124等。
Can someone please provide me with the excel vba codes to do? Thanks.
有人可以为我提供excel vba代码吗？谢谢。
回答by Tony Dallimore
I would not use VBA for this problem. I would type:
我不会使用 VBA 来解决这个问题。我会输入：
=IF(AND(LEFT(B2,1)="F",B2<>"Fruits"),B2,A1)
into A2 and copy down. This seems to give the effect you seek.
进入A2并复制下来。这似乎给了你想要的效果。
If you really want to use VBA, the tested code is:
如果你真的要使用VBA，经过测试的代码是：
Dim CellValue As String
Dim RowCrnt As Integer
Dim RowMax As Integer
With Sheets("Sheet1") ' Replace Sheet1 by the name of your sheet
RowMax = .Cells(Rows.Count, "B").End(xlUp).Row
For RowCrnt = 2 To RowMax
CellValue = .Cells(RowCrnt, 2).Value
If Left(CellValue, 1) = "F" And CellValue <> "Fruits" Then
.Cells(RowCrnt, 1).Value = CellValue
Else
.Cells(RowCrnt, 1).Value = .Cells(RowCrnt - 1, 1).Value
End If
Next
End With
The following explains those aspects of the above code that may be obscure to a newbie.
下面解释了上述代码的那些对新手来说可能比较模糊的方面。
The macro recorder almost always uses Range("A5") or Range("A5:C10") to refer to ranges.
宏记录器几乎总是使用 Range("A5") 或 Range("A5:C10") 来表示范围。
There appear to be a few commands (sorry, don't remember which) for which this format is compulsory but for most purposes Cells(Row, Column) is more convenient.
似乎有一些命令（对不起，不记得是哪个）这种格式是强制性的，但对于大多数用途，单元格（行，列）更方便。
If you want to add 1 to every numeric value in the range "A5:C10" of sheet "Sheet1", the following shows how to do this with Cells:
如果您想为工作表“Sheet1”的“A5:C10”范围内的每个数值加 1，下面显示了如何对单元格执行此操作：
Dim CellValue as String
Dim ColCrnt As Integer
Dim RowCrnt As Integer
With Sheets("Sheet1")
For RowCrnt = 5 to 10
For ColCrnt = 1 to 3 ' "A" to "C"
CellValue = .Cells(RowCrnt, ColCrnt).Value
If IsNumeric(CellValue) Then
.Cells(RowCrnt, ColCrnt).Value = CellValue + 1
End If
Next
Next
End With
There is a time penalty with accessing cells in a worksheet but I have used CellValue more because code can get very cluttered with too many cell references.
访问工作表中的单元格会造成时间损失，但我更多地使用了 CellValue，因为代码会因过多的单元格引用而变得非常混乱。
Note: in "A5" the column comes first but in Cells(5,1) the row comes first.
注意：在“A5”中，列在前，但在 Cells(5,1) 中，行在前。
You can define ranges with Cells. The following sets "A5:C10" of the active sheet to bold:
您可以使用单元格定义范围。以下将活动工作表的“A5:C10”设置为粗体：
Range(Cells(5,1), Cells(10,3)).Font.Bold = True
To address column A of the last row of the active sheet:
要处理活动工作表最后一行的 A 列：
Cells(Rows.Count,"A")
To address the last column of row 1:
要解决第 1 行的最后一列：
Cells(1,Columns.Count)
Ctrl+Up, Ctrl+Down, Ctrl+Left and Ctrl+Right can be used to jump from an empty cell to the next cell with a value in the indicated direction. You can do the same in VBA. But more usefully you can get the value of the row or column to which the cursor would jump without the time penalty of moving the cursor.
Ctrl+Up、Ctrl+Down、Ctrl+Left 和 Ctrl+Right 可用于从一个空单元格跳转到具有指定方向值的下一个单元格。你可以在 VBA 中做同样的事情。但更有用的是，您可以获得光标将跳转到的行或列的值，而无需移动光标的时间损失。
If the cursor was in column B of the last row and you pressed Ctrl+Up, the cursor would jump to the last row with a value in column B. The following sets RowMax to that row number for the active sheet:
如果光标在最后一行的 B 列中并且您按下了 Ctrl+Up，则光标将跳转到 B 列中具有值的最后一行。以下将 RowMax 设置为活动工作表的该行号：
RowMax = Cells(Rows.Count, "B").End(xlUp).Row
To get the last used column of row 27:
获取第 27 行最后使用的列：
ColMax = Cells(27,Columns.Count).End(xlToRight).Column
回答by Salim Hasbaya
Try My Macro
试试我的宏
Option Explicit
Sub test()
Dim My_st$
Dim Ro%, Lr%
With Sheets("Sheet1") ' Replace Sheet1 by the name of your sheet
Lr = .Cells(Rows.Count, "B").End(xlUp).Row
Range("a2").Resize(Lr).ClearContents
For Ro = 2 To Lr
My_st = .Cells(Ro, 2).Value
.Cells(Ro, 1) = _
IIf(My_st Like "[A-Z]#*", My_st, .Cells(Ro - 1, 1))
Next
End With
End Sub
