vba 如果字段有 X，将单元格复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12417991/
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
If field has an X, copy cell to another sheet
提问by Steve
I have an Excel tracker that I put an "X" in a cell every month if a certain activity is accomplished.
我有一个 Excel 跟踪器，如果某个活动完成，我每个月都会在一个单元格中放一个“X”。
This "X" correlates to a range of cells on the same sheet.
这个“X”与同一工作表上的一系列单元格相关。
I want when I click on a command box;
当我点击命令框时，我想要；
- If the cell for January has an "X" copy specific cells on the current page to specific cells on another work sheet.
- If the cell for February has an "X" copy some other specific cells on the current page to some other specific cells on the other worksheet.
- 如果一月的单元格有一个“X”，则将当前页面上的特定单元格复制到另一个工作表上的特定单元格。
- 如果二月的单元格有一个“X”，则将当前页面上的一些其他特定单元格复制到另一个工作表上的一些其他特定单元格。
So on and so forth through December.
以此类推，直到 12 月。
I have the following code (which does not work):
我有以下代码（不起作用）：
Private Sub CommandButton1_Click()
Sheets("MRT").Select
If InStr(1, (Range("L8").Value), "X") > 0 Then
Range("E42:AA42").Select
Selection.Copy
Sheets("Test '12").Select
Cells(3, AP).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub
回答by Jook
Try this:
尝试这个：
Private Sub CommandButton1_Click()
If Sheets("MRT").Range("L8").Value like "*X*" Then
Sheets("MRT").Range("E42:AA42").Copy
Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub
Worked in my test, however you might want to adapt Cells(3,1)and the other position specifiers to your desired targets.
在我的测试中工作过，但是您可能希望Cells(3,1)根据您想要的目标调整其他位置说明符。
edit: forgot about the part with the months ... wait a minute ... here:
编辑：忘记了月份的部分......等一下......这里：
Sub FindSignificant()
Dim SearchString As String
Dim SearchRange As Range, cl As Range
Dim FirstFound As String
Dim sh As Worksheet
' Set Search value
SearchString = "a"
Application.FindFormat.Clear
' loop through all sheets
Set sh = Sheets("MRT")
' Find first instance on sheet
Set cl = sh.Cells.Find(What:=SearchString, _
After:=sh.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cl Is Nothing Then
' if found, remember location
FirstFound = cl.Address
' format found cell
Do
Select Case sh.Cells(cl.Row, 1).Value
Case "december"
sh.Range("E42:AA42").Copy
Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
Case "february"
sh.Range("E42:AA42").Copy
Sheets("Test '12").Cells(3, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
Case Else
'do nothing
End Select
' find next instance
Set cl = sh.Cells.FindNext(After:=cl)
' repeat until back where we started
Loop Until FirstFound = cl.Address
End If
End Sub
this code origins from here
这段代码来自这里
You would have to adapt the select case, but i really would think about solving this without VBA, if it is not necessary ;)
您将不得不调整选择案例，但如果没有必要，我真的会考虑在没有 VBA 的情况下解决这个问题；)
