Hi
Sub UpdateYear()
Dim sCell as Variant, tCell as Variant
Dim EmptyCell as Boolean
EmptyCell = False
For i = 2 To 10000
sCell = Sheets("Historical").Range("B"& i).Value 'Source Value
("Date")
tCell = Sheets("Historical").Range("AF"& i).Value 'Target
Value("YYYY")
If sCell<> "" Then
Sheets("Historical").Range("AF"& i).Value =
Sheets("Historical").Range("B"& i).Value
Sheets("Historical").Range("AF"& i).NumberFormat = "YYYY"
Else
EmptyCell = True
End If
If EmptyCell then
msgbox "Empty cell at B"&i
Exit For
end if
Next i
End Sub
regards
Paul
Hi Paul and thank you for your idea.
It works well, but!
What it does is actually places a date format in sCell and the formatted
result although displays the "YYYY" result, alas it effect other
sumproduct lookups.
What I really need is the actual Number Value eg 2012, not the
Format(Date, "YYYY").value
I was kind of playing with this, hybrid of your code, but of course it
doesn't work:
Sub UpdateYear()
Dim sCell As Variant, tCell As Variant
Dim eCell As Boolean
Dim myValue As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
eCell = False
For i = 2 To 10000
sCell = Sheets("Historical").Range("B" & i).Value 'Source Value
("Date")
tCell = Sheets("Historical").Range("AF" & i).Value 'Target Value
("YYYY")
myValue = Text(sCell, "YYYY").Value
If sCell <> "" Then
tCell.Value = myValue
Else
eCell = True
End If
If eCell Then
Exit For
End If
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Again, thanks for the assist.
Mick.