如何用VBA粘贴值而非公式？含指定VLOOKUP公式取值需求

Let’s break this down into two parts: first, the general approach to pasting values instead of formulas in VBA, then a tailored solution for your specific VLOOKUP formula use case.

1. General Methods to Paste Values in VBA #

There are two efficient ways to do this—one mirrors the Excel UI’s "Paste Values" action, and the other skips copy-paste entirely for faster performance:

Method 1: Using PasteSpecial #

This is great if you’re copying from one range to another, just like you’d do manually:

' Example: Copy B2:B100 and paste values to D2:D100
Range("B2:B100").Copy
Range("D2:D100").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False ' Clear the copy buffer to free memory

Method 2: Assign Values Directly (Faster for Large Data) #

Skip the copy-paste step by setting the target range’s value to match the source’s value directly:

' Example: Set D2:D100 to the values of B2:B100
Range("D2:D100").Value = Range("B2:B100").Value

2. Specific Solution for Your VLOOKUP Formula #

You want to calculate the result of this formula and paste the value into a target cell (I’ll assume you mean cell A6318 based on the formula’s lookup value—adjust the target cell as needed):

=IFERROR(VLOOKUP($A6318,'https://-/-/-/-/-/-/-/[-.xlsx]-'!$A:$BO,2,FALSE),"")

Instead of inserting the formula then converting it to a value, we can compute the result directly in VBA and assign it to the cell. Here’s the code:

Sub PasteVLookupResultAsValue()
    Dim targetCell As Range
    Dim lookupValue As Variant
    Dim sourceWorkbook As Workbook
    Dim sourceRange As Range
    Dim result As Variant
    
    ' Set your target cell (update sheet name and cell reference to match your needs)
    Set targetCell = ThisWorkbook.Sheets("YourSheetName").Range("A6318")
    
    ' Get the lookup value from cell A6318 (matches your formula's $A6318)
    lookupValue = targetCell.Value
    
    On Error Resume Next ' Handle cases where the external workbook isn't accessible
    ' Open the external workbook (replace the URL/path with your actual file location)
    Set sourceWorkbook = Workbooks.Open("https://-/-/-/-/-/-/-/-.xlsx")
    On Error GoTo 0
    
    If Not sourceWorkbook Is Nothing Then
        ' Define the source range from the external workbook (update sheet name)
        Set sourceRange = sourceWorkbook.Sheets("ExternalSheetName").Range("A:BO")
        
        ' Perform the VLOOKUP calculation in VBA
        result = Application.VLookup(lookupValue, sourceRange, 2, False)
        
        ' Handle errors (equivalent to the IFERROR in your formula)
        targetCell.Value = IIf(IsError(result), "", result)
        
        ' Close the external workbook without saving changes
        sourceWorkbook.Close SaveChanges:=False
    Else
        ' If the workbook couldn't open, set target cell to empty and notify
        targetCell.Value = ""
        MsgBox "Failed to open the external workbook. Check permissions or file path.", vbExclamation
    End If
End Sub

Quick Adjustments You’ll Need to Make: #

• Replace YourSheetName with the name of the sheet where your target cell lives.
• Replace ExternalSheetName with the actual sheet name from the external workbook.
• Double-check the external workbook’s URL/path to ensure VBA can access it (for cloud files, confirm you’re signed in with proper permissions).

If the formula is already in a cell and you just want to convert it to a value, use this simple one-liner:

' Replace "YourFormulaCell" with the cell containing your VLOOKUP formula
Range("YourFormulaCell").Value = Range("YourFormulaCell").Value

内容的提问来源于stack exchange，提问作者Lim Yi Long