VBA Code to Perform Paste Special of Values over Formula when Date is matched.

[Mr B J B]

Hi,

I am struggling to write the correct VBA code for the following issue.

I have a worksheet which has formulas in that pulls information from another worksheet using Index Match, this works fine.

However I can't seem to write the correct code to:
1. Copy the data in the cells where these formulas are and perform a paste special into the same cells thus overwriting the formulas with the values that have pulled through. The data will be in columns H,I,J and K and potentially rows 2 to 10,000.
2.This action needs to happen when a date is entered into a cell in column L in the same row matches a date which is in cell A1.
3.This action will happen to multiple rows of data but not all, so the code needs to cycle through all rows which have data entered into.

I hope someone is able to help me with the above.

Thank you in advance

 

[Michael M]

So, just to clarify....If the value in cell L is equal to the value in cell A1, ALL values in that row in Cols H,I, J,K will be converted from formulas to values ??
Is the reference cell always going to be A1 or will it be A and that row ??

 

[Mr B J B]

Hi Michael, yes I am trying to get the formulas to be converted to values when value in cell L = cell A1. Cell A1 will always be the reference and will not move.

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If Range("L" & r).Value = Range("A1").Value Then
        With Range("H" & r & ":K" & r)
            .Value = .Value
        End With
    End If
Next r
End Sub