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]

[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl63, width: 112"]val1[/TD]
[TD="class: xl63, width: 112"]val2[/TD]
[TD="class: xl63, width: 112"]val3[/TD]
[TD="class: xl63, width: 112"]sum[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]=SUM(A2:C2)[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]90[/TD]
[TD]66[/TD]
[TD]=SUM(A3:C3)[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]46[/TD]
[TD]100[/TD]
[TD]=SUM(A4:C4)[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]1[/TD]
[TD]33[/TD]
[TD]=SUM(A5:C5)[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]73[/TD]
[TD]19[/TD]
[TD]=SUM(A6:C6)[/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD]69[/TD]
[TD]47[/TD]
[TD]=SUM(A7:C7)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]47[/TD]
[TD]78[/TD]
[TD]=SUM(A8:C8)[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]17[/TD]
[TD]7[/TD]
[TD]=SUM(A9:C9)[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]44[/TD]
[TD]98[/TD]
[TD]=SUM(A10:C10)[/TD]
[/TR]
</tbody>[/TABLE]

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