Tengo una función de VLookup en la que utilicé .value en lugar de .formula para eliminar la referencia de Vlookup. Sin embargo, cuando hago clic en el campo, la fórmula de VLookup sigue ahí. ¿Cuál es la forma correcta de eliminar la fórmula? copy / paste special Values?

Dim WbScorecard As Workbook
Set WbScorecard = Workbooks("M6 SCORECARD TEMPLATE.xlsm")
    
Dim WsWeeklyDiv As Worksheet
Set WsWeeklyDiv = WbScorecard.Worksheets("Weekly Division")
       
With WsWeeklyDiv
  'VLookup Group Data
  Dim VLookupWkDiv As String
                VLookupWkDiv = "=VLOOKUP(I15,'[M6 SCORECARD-Vlookups.xlsm]WEEKLY DIVISION'!$C:$D,2,FALSE)"
    
  Dim VLookupWkDiv2 As String
                VLookupWkDiv2 = "=VLOOKUP(I15,'[M6 SCORECARD-Vlookups.xlsm]WEEKLY DIVISION'!$C:$E,3,FALSE)"
    
  Dim Target As Range
  Set Target = .Range("A15", .Range("A" & .Rows.Count).End(xlUp))
    'VLookup in "J15"
    Set Target = Target.Offset(, 9)
      Target.Value = VLookupWkDiv
            
    'VLookup in "K15"
    Set Target = Target.Offset(, 1)
       Target.Value = VLookupWkDiv2
                    
End With
0
CDay 31 ago. 2020 a las 17:21

1 respuesta

La mejor respuesta
Set Target = Target.Offset(, 9)

' set target cell's formula
Target.Formula = VLookupWkDiv

' set the value/result of the formula
' to the value of the cell
Target.Value = Target.Value
2
test 31 ago. 2020 a las 14:56