how to remove values once cells been updated

[Patriot2879]

hi I have the code below where I have added a vlookup, but once the cells are updated I want to remove the vlookup, I have tried '.value=.value' but that didn't work can you help please?

Private Sub CommandButton1_Click()
 Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
      Set r = Range("AP2", Range("AP" & Rows.Count).End(xlUp))
    Range("A2").Resize(r.Rows.Count).Value = r.Value
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0
Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
Range("C2:C" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:D,3,FALSE)"
Range("B2:B" & Lastrow).Formula = "=IF(A2=A1,0,1)"
Range("D2:D" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:E,4,FALSE)"
Range("J2:J" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:K,10,FALSE)"
Range("K2:K" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:L,11,FALSE)"
Range("L2:L" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:M,12,FALSE)"
Range("N2:N" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:O,14,FALSE)"
End Sub

 

[Repush]

given the fact that setting a breakpoint and then run the code does "nothing" - as you said yesterday - I doubt that the code is running anyway
try stepping through the code using F8 . . and see what happens

 

[Patriot2879]

hi I can only see it working up to this line when pressing F8. hope you advise.

Range("N2:N" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:O,14,FALSE)"

 

[Repush]

what are the values in vArr?

 

[Patriot2879]

I don't know what you mean sorry, I put the vArr in as I was advised to but don't know what it means

 

[Repush]

you should invest some time in reading how to operate the VBA-editor, how to inspect local variables and so on (no tips from me there, i'm afraid )
because without your info I can't help you

 

[Patriot2879]

what info do you need? what do you mean you mean by the values of Varr?

 

[Repush]

val1	val2	val3	sum
87	0	45	=SUM(A2:C2)
9	90	66	=SUM(A3:C3)
81	46	100	=SUM(A4:C4)
28	1	33	=SUM(A5:C5)
67	73	19	=SUM(A6:C6)
98	69	47	=SUM(A7:C7)
15	47	78	=SUM(A8:C8)
57	17	7	=SUM(A9:C9)
24	44	98	=SUM(A10:C10)

<tbody>
</tbody>

Sub SetVals()
    Dim MyRange As Range
    Dim vArr()
    Set MyRange = [D2:D10]
    vArr = MyRange.Value
    MyRange = vArr
End Sub

OK . . . let's try this.
Make a new simple xlsm with the data above (just random numbers with sums in Col D), place the code and run it . . .
What happens?

 

[Patriot2879]

hi that works it clears the =SUM bit from the cell
how do I put that into my code? please

 

[Repush]

well . . the point is: it is the same code, this is just proof of principle
that's why it's important to know the values stored in the vArr array by opening the local variable-window from the view-menu

 

[Patriot2879]

oww, what do you mean by the values stored in vArr? I don't have a variable window