Excel VBA Updates not functioning as intended

Thanks for that thoughtful follow-up — really appreciate the careful read.

You’re absolutely right: most of the edits were cleanup (naming consistency, structure, better error handling, and avoiding duplicated Exit Sub branches). But yes, the two points you noted — using Application.InputBox with Type:=1 and separating vDate as an actual Date variable — are what primarily affect the matching logic.

The root of the original issue was indeed a type mismatch during the .Find operation. The original code was searching for a text string like "10/01/2025", while the worksheet cells contained true date serials formatted as m/d/yyyy. Since .Find with LookIn:=xlValues compares underlying values, not visible formatting, the string "10/01/2025" didn’t match the numeric date value.

By explicitly converting both sides to a true date (vDate = CDate(NM)) and searching for that date value, Excel’s internal comparison works correctly regardless of how the cell is formatted (whether 1/4/2025 or 01/04/2025).

The other side benefit of the rewrite is that it ensures formulas pasted into the “Slide Prep” sheet are treated as real formulas — not text — by resetting number formats before assigning them.

I’ll definitely share if anything else interesting turns up, but yes, it seems to come down to how .Find interprets date strings versus serials depending on locale and cell formatting.

Thanks again for the thoughtful comment — this kind of cross-check is exactly why the community is great.

… I rewrite it in a slightly more bulletproof version with error handling and dynamic column handling for Slide Prep. That would make it future-proof….i hope 🙂i dont test it.

Option Explicit

Sub CMLUpdateV4()
Dim wb As Workbook
Dim wsData As Worksheet, wsSlide As Worksheet
Dim rngDateRow As Range, rngFound As Range
Dim dtInput As Variant, NM As String, vDate As Date
Dim blnValidDate As Boolean
Dim col As Long, lastCol As Long

```
On Error GoTo Cleanup

'--- Optimize performance
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'--- Close other workbooks
For Each wb In Application.Workbooks
    If Not (wb Is ThisWorkbook) Then wb.Close SaveChanges:=False
Next wb

'--- Ask user for a valid date
blnValidDate = False
Do
    dtInput = Application.InputBox("Please Enter File Submission Month as MM/DD/YYYY:", _
                                   "Date Entry", , , , , , 1)
    If dtInput = False Then
        MsgBox "Update cancelled by user.", vbInformation
        GoTo Cleanup
    End If
    
    If IsDate(dtInput) Then
        NM = Format(CDate(dtInput), "MM/DD/YYYY")
        vDate = CDate(NM)
        blnValidDate = True
    Else
        MsgBox "Invalid Date Format. Please re-enter in MM/DD/YYYY format.", vbExclamation
    End If
Loop Until blnValidDate

'--- Reference worksheets
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsSlide = ThisWorkbook.Worksheets("Slide Prep")

'--- Check if date already exists in Data row 2
Set rngDateRow = wsData.Rows(2)
Set rngFound = rngDateRow.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole)

If Not rngFound Is Nothing Then
    MsgBox "Date " & NM & " already exists on the Data sheet. Please review.", vbInformation
    GoTo Cleanup
End If

'=======================
' Update Data tab
'=======================
With wsData
    .Columns("C:C").Insert
    .Columns("D:D").Copy
    .Columns("C:C").PasteSpecial xlPasteAll
    .Range("C2").Value = vDate
    .Range("C2").NumberFormat = "m/d/yyyy"
    
    ' Clear old content blocks
    .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents
    
    ' Ensure column O is static values
    .Columns("O:O").Copy
    .Columns("O:O").PasteSpecial xlPasteValues
End With

'=======================
' Update Slide Prep tab
'=======================
With wsSlide
    .Rows("2:2").Insert
    .Range("A2").Value = vDate
    .Range("A2").NumberFormat = "m/d/yyyy"
    
    ' Copy formulas from previous row (B3:H3)
    .Range("B3:H3").Copy
    .Range("B2:H2").PasteSpecial xlPasteAll
    
    ' Reset number format for new formula columns
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 9), .Cells(2, lastCol)).NumberFormat = "General"
    
    ' Insert dynamic XLOOKUP formulas B2 to last column
    For col = 2 To lastCol
        .Cells(2, col).Formula = "=XLOOKUP(" & .Cells(1, col).Address(False, False) & _
                                 ",Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))"
    Next col
    
    ' Convert row 14 to values (per original code)
    .Rows("14:14").Copy
    .Rows("14:14").PasteSpecial xlPasteValues
End With

'--- Success message
MsgBox "New File Date " & NM & " added and sheets updated." & vbCrLf & _
       "Proceed to Data tab to record Enrollment Line Counts.", vbInformation
```

Cleanup:
'--- Restore defaults
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

```
If Err.Number <> 0 Then
    MsgBox "An error occurred: " & Err.Description, vbCritical
End If
```

End Sub