PC Review


Reply
Thread Tools Rate Thread

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
 
Reply With Quote
 
 
 
 
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


 
Reply With Quote
 
 
 
 
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.


 
Reply With Quote
 
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.
 
Reply With Quote
 
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.


 
Reply With Quote
 
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.
 
Reply With Quote
 
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 -


 
Reply With Quote
 
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
printing Little Current month and Little Next month on Banner when it should little PRIOR month and little Next month. jake_allen10@hotmail.com Microsoft Outlook 0 3rd Nov 2006 08:30 PM
Blank screen with a box in upper left corner that has a little red square, a little green circle, and a little blue triangle gdelcarlo Windows XP Basics 1 5th May 2006 06:23 AM
Learning little by little, but could still use some help Kevin Kimbrell via AccessMonster.com Microsoft Access Getting Started 1 1st Jun 2005 08:13 PM
HELP NEEDED FAST!!! HELP HELP HELP HELP HELP HELP FAST HELP NEEDED Jonathan Windows XP General 10 13th Jan 2005 03:22 AM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:59 PM.