Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > A little help needed please...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 259
Default A little help needed please...
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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default A little help needed please...
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 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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 812
Default A little help needed please...
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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 259
Default A little help needed please...
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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default 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.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 259
Default A little help needed please...
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 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 QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default A little help needed please...
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 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 *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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 259
Default A little help needed please...
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 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 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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Rows With Non-Needed Data between Needed Data Daren Excel Worksheet Functions 2 September 30th 08 06:47 PM
sum(if...) help needed! Jarek Excel Discussion (Misc queries) 3 March 11th 08 07:48 PM
VBA help needed please paul[_17_] Excel Programming 1 July 17th 07 06:05 PM
More help needed :-( ChrisMattock Excel Worksheet Functions 5 June 28th 06 01:03 PM
Help needed Michael168[_12_] Excel Programming 1 October 2nd 03 05:44 PM


All times are GMT +1. The time now is 08:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement