Macro behaving unusually - replaces non matching criteria with unusual criteria-CodePudding

I've been working on a macro to copy/paste formulas in columns where the date is less than today's date. I got this working successfully a few minutes ago, and this almost works as intended, but for some reason when I run the macro it goes beyond the criteria and replaces non matching criteria to fit the criteria. Can someone look at my code and point out what might be causing this?

I have a spreadsheet with data from A1:I3 - Row 1 contains dates, Rows 2&3 contain formulas. The dates in row 1 run from 03/01/22 - 03/09/22 - my macro looks for any dates less than today's date & copies & pastes the formulas as values. This works well, but for some reason - the dates in H & I which are 03/08/22 and 03/09/22 respectively, for some reason they're getting replaced with 03/07/22. No idea how/why this is happening.

Here is the code that I've got:

Dim K As String
  K = Date
  MsgBox K

Dim i As Integer

For i = 1 To 9

If (Cells(1, i).Value < K) Then Cells(1, i).EntireColumn.Copy
Cells(1, i).PasteSpecial xlPasteValues
Next i



End Sub

CodePudding user response：

Your logic only checks when copying, but not when pasting.

Cells(1, i).PasteSpecial xlPasteValues

This is executed for every single i, regardless of whether (Cells(1, i).Value < K) is True or False. You need to use the multi-line If...End If syntax.

If Cells(1, i).Value < K Then 
    Cells(1, i).EntireColumn.Copy
    Cells(1, i).PasteSpecial xlPasteValues
End If

To make this better though, avoid working with a String representation of a date, and bypass the clipboard:

If Cells(1, i).Value < Date Then
    Columns(i).Value = Columns(i).Value
End If