vba 宏以查找值并复制并粘贴到另一个单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15150627/
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
macro to find values and copy & paste inti another cell
提问by kish
I want to create simple tool using by macro which have to find "R" text in D column if it's match then copy that cell value and past into "L" column.
我想使用宏创建简单的工具，如果匹配，必须在 D 列中找到“R”文本，然后将该单元格值复制到“L”列中。
If run the macro for below script i could get exact values but it's has been one cell.so anybody help to me to do find entire D column.
如果运行下面脚本的宏，我可以获得确切的值，但它是一个单元格。所以有人帮我找到整个 D 列。
D L
1111_r 1111_r
0000
22222
348_16re
111
222
333_16re
Dim c As Range
With ActiveWorkbook.Sheets("PLMSync_NetChange")
Set c = .Range("D1:D20").Find(What:="_R", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
On Error GoTo NotFoundErr:
c.Offset(0, 15).Value = c.Value
End With
Exit Sub
NotFoundErr:
Debug.Print "value not found"
End Sub
Thank you very much.It's working now......after this process have to do compare L column values to A cloumn With in limits. want to take before _valus from L column and find to A column from below item iD cell to above bom update cell (with in limit).If it is matched then copy and paste into M column.
非常感谢。它现在正在工作......在这个过程之后必须将 L 列值与 A cloumn 进行比较。想要从 L 列中获取 before _values 并从项目 iD 单元格下方到 BOM 更新单元格上方找到 A 列（在限制中）。如果匹配，则复制并粘贴到 M 列中。
Here i have attached example which is shown clearly..
在这里我附上了一个例子，它显示得很清楚..
A B C D L M
BOM Update report for car
Summary: Additions=14;Removals=10;Changes=3;Same=20
Add Remove Remove
Item Id Revision ProFeatureID Item Id
xxxxxx 0 795 3S2093_L
xxxxxx 0 802 3S2093_L
xxxxxx 0 790 3S2093_L
yyyyyy 0 720 3S2093_L
yyyyyy 0 817 3S2093_L
yyyyyy 0 740 3S2093_L
zzzzzz 0 732 11111_re 11111_re 11111
zzzzzz 0 746 11111_re 11111_re 11111
zzzzzz 0 758 11111_re 11111_re 11111
zzzzzz 0 766 11111_re 11111_re 11111
11111 2 774
11111 2 777
11111 2 780
11111 2 783
BOM Update report for bike
采纳答案by kish
Sub sample()
Dim lastRow As Long
Dim rng As Range
With Sheets("PLMSync_NetChange")
.Activate
lastRow = .Range("D65000").End(xlUp).Row
For i = 1 To lastRow
If (InStr(1, .Range("D" & i).Value, "r", vbTextCompare) > 0) Then
.Range("L" & i).Value = .Range("D" & i).Value
End If
Next
End With
Exit Sub
NotFoundErr:
Debug.Print "value not found"
End Sub
回答by kish
Dim LvRow As Integer
Dim LvCol As Integer
lastrow = Range("D65354").End(xlUp).Row
LvCol = 4
For LvRow = 1 To lastrow
If InStr(1, Cells(LvRow, LvCol), "R",vbtextcompare) > 0 Then
Cells(LvRow, LvCol).Select
Cells(LvRow, LvCol + 8).Value = Cells(LvRow, LvCol)
End If
Next
回答by kish
Dim lvrow As Integer
将 lvrow 变暗为整数
lastrow = Range("A65354").End(xlUp).Row
lastrow = Range("A65354").End(xlUp).Row
For lvrow = 1 To lastrow
对于 lvrow = 1 到 lastrow
If (InStr(1, Cells(lvrow, 12), "1") > 0) Then
如果 (InStr(1, Cells(lvrow, 12), "1") > 0) 然后
If CStr(Mid(Cells(lvrow, 12), 1, InStr(1, Cells(lvrow, 12), "_") - 1) )=CStr(Cells(lvrow, 1)) Then
Cells(lvrow, 13) = Cells(lvrow, 1)
Cells(lvrow, 14) = "Match Found"
End If
End If
万一
Next
下一个
if you want to get some other column value to be pasted in col M jus change the column no.
如果您想将其他列值粘贴到 col M 中，请更改列号。
