A little help needed please...
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
On Feb 16, 10:16*am, Vacuum Sealed wrote:
On 15/02/2012 11:40 PM, merjet wrote: It's hard for me grasp what the author intended. It doesn't work
because the second argument of each Cells is a Range rather than a
number or letter indicating the column. With that fixed and dates in
A1:B5, it changes B1:B5 to the values in A1:A5 and formats B1:B5 to
show only the year.
Appreciate the assist guy's
I decided to look at it slightly different and came up with the following:
Sub UpdateYear()
Sheets("Historical").Select
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
Exit For
End If
If sCell < "" Then
Sheets("Historical").Range("AF" & i).Value =
Sheets("Historical").Range("B" & i).Value
Sheets("Historical").Range("AF" & i).NumberFormat = "YYYY"
Else
Exit For
End If
Next i
End Sub
It gets hung up and I have to interrupt the code to stop it.
So, some background on what it is I'm trying to do here.
If the intersecting Row of Column B has a Date in it, then the same
corresponding Row of Column AF = that Dates Value (Formatted as "YYYY")
and keeps doing this until the Loop hits a NullCell and exits the loop.
Thanks again
Mick.
|