Change formula to next cell down

Question

I am trying to compare values from a vlookup to a static value in the column to the left, and then paste the value if it matches.

Column J contain static values
Column K contains a vlookup formula referencing another sheet

I need to loop through column K, and if the value from the vlookup matches the value in the same row in column J, then paste the value into column K.

So if cell J2 = 240.89, and the value of the vlookup in cell K2 = 240.89, then paste the value 240.89 into K2.

So far, the code I have loops through the range but stops at the last pasted value and ignores the vlookup formulas.

Here is the code I have:

Option Explicit

Sub CheckValue()

Dim myRange As Range
Dim cell As Range
Set myRange = Range("K2:K3194")

For Each cell In myRange
    If cell.Value = cell.Offset(0, -1) Then
    cell.Copy
    cell.PasteSpecial xlPasteValues
    End If
    Next cell
End Sub

Any help is appreciated!

Answer 1

Try

For Each cell In myRange
    If cell.Value2 = cell.Offset(0, -1).Value2 Then
        cell.value = cell.value
    End If
Next cell

or maybe use a WorksheetFunction.Round to match the values to a specific precision. I feel like this is more the issue than anything.

Answer 2

Just for fun another option:

Sub Test()

Dim ws as Worksheet: Set ws = Thisworkbook.Worksheets("Sheet1")
ws.Range("K2:K3194").Value = ws.Evaluate("IF(K2:K3194=J2:J3194,J2:J3194,FORMULATEXT(K2:K3194))")

End Sub

^{Note: This requires FORMULATEXT, a function available since Excel2013.}