timestamp

So I am trying to place a static date in a cell if Column A has a number in it and Column C, where the date will go is blank I have been looking for awhile now and cant seem to get anything to work for me here is my code

Sub Udpate()
'
' Update Macro
' Keyboard Shortcut: Ctrl Shift + U
'
Dim lst As Long
Sheets("Input").Range("B2:C51").Copy
With Sheets("PerformanceMatrix")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteValues
End With


Dim cell As Range
Dim Range8 As Range

Set Range8 = Sheets("PerformanceMatrix").Range("A2:A")
For Each cell In Range8
    If cell = "" Then
        cell.Offset(0, 2) = "0"
    Else
        cell.Offset(0, 2) = Date
    End If
Next cell


Dim x  As Long
    Dim LastRow As Long
     
    LastRow = Range("B65536").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A2:A" & x), Range("A" & x).Text) > 1 Then
            Range("A" & x).EntireRow.Delete
        End If
    Next x
    
Dim ws1 As Worksheet:   Set ws1 = Sheets("PerformanceMatrix")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Input")
Dim Range1 As Range, Range2 As Range, icell As Range, iFind As Range, Range3 As Range

Set Range1 = ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
Set Range2 = ws2.Range("B2:B" & ws2.Range("B" & Rows.Count).End(xlUp).Row)
Set Range3 = ws1.Range("C2:C" & ws1.Range("C" & Rows.Count).End(xlUp).Row)

For Each icell In Range1
    If IsNumeric(icell.Value) Then
        Set iFind = Range2.Find(What:=icell.Value, LookIn:=xlValues, lookat:=xlWhole)
            If iFind Is Nothing Then
                icell.Offset(0, 3).Value = Date
            End If
    End If
Next icell


Range("A2").Select

End Sub

Hi

Your first mistake is in this line:

Set Range8 = Sheets("Sheet1").Range("A2:A" )

you haven't specified an end row so your macro stops at that point.


This amendment fixes that and inserts your date stamp.

Set Range8 = Sheets("Sheet1").Range("A2:A" & Selection.SpecialCells(xlCellTypeLastCell).Row)

This whole thing is a mess. mehmetcik was correct on the first section but later on down it is done correctly

Set Range1 = ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)

However, both spots are flawed. (example: If cell = "" is testing column A for a blank value but you need to be testing it for a number)

This small section is probably the closest to correct (corrections added)

Dim ws1 As Worksheet:   Set ws1 = Sheets("PerformanceMatrix")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Input") 'dont need
Dim Range1 As Range, icell As Range

Set Range1 = ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
Set Range2 = ws2.Range("B2:B" & ws2.Range("B" & Rows.Count).End(xlUp).Row) 'don't need
Set Range3 = ws1.Range("C2:C" & ws1.Range("C" & Rows.Count).End(xlUp).Row) 'don't need

For Each icell In Range1
    If IsNumeric(icell.Value) And icell.Offset(0, 2).Value = "" Then
        icell.Offset(0, 2).Value = Date
    End If
Next icell

Thank you both very much I got it working and made those corrections