# A little help needed please...

Vacuum Sealed
Guest
Posts: n/a

 15th Feb 2012
Hi all

For some reason I can't see the forest through the trees in this If,
Then, Next code...

Sub UpdateYear()
Dim sCell As Range
Dim tCell As Range
Dim i As Integer
For i = 1 To 5
Set sCell = Range("A1:A5")
Set tCell = Range("B1:B5")
If Not Cells(i, tCell) = "" Then
Cells(i, tCell).Value = Cells(i, sCell)
Cells(i, tCell).NumberFormat = "YYYY"
End If
Next i
End Sub

As always

TIA
Mick

Paul Robinson
Guest
Posts: n/a

 15th Feb 2012
Hi
Maybe this?
Sub UpdateYear()
Dim sCell As Range
Dim tCell As Range
Dim i As Integer
For i = 1 To 5
Set sCell = Range("A1:A5")
Set tCell = Range("B1:B5")
If Not tCell(i, 1).Value = "" Then
tCell(i, 1).Value = sCell(i, 1).Value
tCell(i,1).NumberFormat = "YYYY"
End If
Next i
End Sub

More probably this?
Sub UpdateYear()
Dim sCell As Range
Dim tCell As Range
Dim i As Integer
Set sCell = Range("A1:A5")
Set tCell = Range("B1:B5")
For i = 1 To 5
If Not tCell(i, 1).Value = "" Then
tCell(i, 1).Value = sCell(i, 1).Value
tCell(i,1).NumberFormat = "YYYY"
End If
Next i
End Sub

regards
Paul

On Feb 15, 11:47*am, Vacuum Sealed <(E-Mail Removed)> wrote:
> Hi all
>
> For some reason I can't see the forest through the trees in this If,
> Then, Next code...
>
> Sub UpdateYear()
> Dim sCell As Range
> Dim tCell As Range
> Dim i As Integer
> For i = 1 To 5
> Set sCell = Range("A1:A5")
> Set tCell = Range("B1:B5")
> If Not Cells(i, tCell) = "" Then
> * * *Cells(i, tCell).Value = Cells(i, sCell)
> * * *Cells(i, tCell).NumberFormat = "YYYY"
> End If
> Next i
> End Sub
>
> As always
>
> TIA
> Mick

merjet
Guest
Posts: n/a

 15th Feb 2012
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.

Vacuum Sealed
Guest
Posts: n/a

 16th Feb 2012
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.

Paul Robinson
Guest
Posts: n/a

 16th Feb 2012
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 <(E-Mail Removed)> 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.

Vacuum Sealed
Guest
Posts: n/a

 17th Feb 2012
On 16/02/2012 11:06 PM, Paul Robinson wrote:
> 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<(E-Mail Removed)> 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.

>

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.

Paul Robinson
Guest
Posts: n/a

 17th Feb 2012
Hi
Replace
Sheets("Historical").Range("AF" & i).Value =
Sheets("Historical").Range("B" & i).Value
Sheets("Historical").Range("AF" & i).NumberFormat = "YYYY"

with
Sheets("Historical").Range("AF" & i).Value =
Year(Sheets("Historical").Range("B" & i).Value)

regards
Paul

On Feb 17, 11:15*am, Vacuum Sealed <(E-Mail Removed)> wrote:
> On 16/02/2012 11:06 PM, Paul Robinson wrote:
>
>
>
> > 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<(E-Mail Removed)> *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.

>
> 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.- Hide quoted text -
>
> - Show quoted text -

Vacuum Sealed
Guest
Posts: n/a

 20th Feb 2012
On 17/02/2012 11:12 PM, Paul Robinson wrote:
> Hi
> Replace
> Sheets("Historical").Range("AF"& i).Value =
> Sheets("Historical").Range("B"& i).Value
> Sheets("Historical").Range("AF"& i).NumberFormat = "YYYY"
>
> with
> Sheets("Historical").Range("AF"& i).Value =
> Year(Sheets("Historical").Range("B"& i).Value)
>
> regards
> Paul
>
> On Feb 17, 11:15 am, Vacuum Sealed<(E-Mail Removed)> wrote:
>> On 16/02/2012 11:06 PM, Paul Robinson wrote:
>>
>>
>>
>>> 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<(E-Mail Removed)> 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.

>>
>> 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.- Hide quoted text -
>>
>> - Show quoted text -

>

That did the trick nicely, thank you Paul.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post jake_allen10@hotmail.com Microsoft Outlook 0 3rd Nov 2006 08:30 PM gdelcarlo Windows XP Basics 1 5th May 2006 06:23 AM Kevin Kimbrell via AccessMonster.com Microsoft Access Getting Started 1 1st Jun 2005 08:13 PM Jonathan Windows XP General 10 13th Jan 2005 03:22 AM -\$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM

Features