执行 Vlookup 后的 VBA 复制和粘贴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26889650/
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
VBA Copy and Paste after Vlookup is performed
提问by VBA Noob
Is there a more efficient way to copy and paste value after the vlookup code is executed? The idea is that after the vlookup is executed in almost 10,000+ cells I want to copy and paste the values as it makes the excel file more stable.
在执行 vlookup 代码后，是否有更有效的方法来复制和粘贴值？这个想法是，在近 10,000 多个单元格中执行 vlookup 后，我想复制并粘贴这些值，因为它使 excel 文件更稳定。
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim X As Long
Dim Z As Long
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col C
OutputLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Y = 17 To 28 'Q to AB
For X = 2 To OutputLastRow
If InStr(1, .Range("C" & X), "PO Materials") + InStr(1, .Range("C" & X), "PO Labor") > 0 And Cells(2, Y) = "Forecast" Then
'Apply formula
.Cells(X, Y).Formula = _
"=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A:$L$" & SourceLastRow & ",Match(" & Cells(1, Y).Address & ",'" & sourceSheet.Name & "'!$A:$AD,0),0)"
.Cells(X, Y).Select
.Cells(X, Y).Copy
.Cells(X, Y).PasteSpecial Paste:=xlPasteValues
End If
Next
Next
End With
End Sub
回答by Sam
With .Cells(X, Y)
.Value = .Value
End With
回答by VBA Noob
Change .Formula to .Value so VBA directly executes the vlookup and then pastes the value
将 .Formula 更改为 .Value 以便 VBA 直接执行 vlookup 然后粘贴值
.Cells(X, y).Value = _
Evaluate("=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A:$L$" & SourceLastRow & ",Match(" & Cells(1, y).Address & ",'" & sourceSheet.Name & "'!$A:$AD,0),0)")
