Change date formula to Value

[Michael]

I have a spreadsheet to record serial numbers already populated in column A
and the date the specific serial number was issued. The issuer puts a code
in column C which automatically assigns a date in column E and some location
details in columns F to H. What I need to do is run a macro which saves the
file but changes column E to a value if column C is not blank. Column C can
have gaps between rows.

Any ideas would be greatly appreciated

Michael

 

[Joel]

why don't you use a worksheet_Change function that when a date is entered in
column c todays date is automatically enetered in column E.

Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If cell.Column = 3 Then
cell.Offset(0, 2) = Date
End If

Next cell

End Sub

 

[Otto Moehrbach]

Michael
This little macro will do that. HTH Otto
Sub EValue()
Dim rColC As Range
Dim i As Range
Set rColC = Range("C2", Range("C" & Rows.Count).End(xlUp))
For Each i In rColC
If Not IsEmpty(i.Value) Then
i.Offset(, 2).Copy
i.Offset(, 2).PasteSpecial xlPasteValues
End If
Next i
End Sub

 

[Michael]

Thanks very much for this it has saved me a lot of head scratching!!!

Michael