CODEWITHSUNDEEP
excelvba
CDay
CDay

Reputation: 111

How to copy/paste Values after using VLookup

I have a VLookup funtion where I used .value instead of .formula to remove the Vlookup reference. However, when I click into the field, the VLookup formula is still there. What is the proper way to remove the formula? 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

Upvotes: 0

Views: 737

Answers (1)

test
test

Reputation: 2639

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

Upvotes: 2

Related Questions