A little help needed please...

Discussion in 'Microsoft Excel Programming' started by Vacuum Sealed, Feb 15, 2012.

  1. 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
     
    Vacuum Sealed, Feb 15, 2012
    #1
    1. Advertisements

  2. 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
     
    Paul Robinson, Feb 15, 2012
    #2
    1. Advertisements

  3. Vacuum Sealed

    merjet Guest

    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.
     
    merjet, Feb 15, 2012
    #3
  4. 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, Feb 16, 2012
    #4
  5. 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.
     
    Paul Robinson, Feb 16, 2012
    #5
  6. 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<> 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.
     
    Vacuum Sealed, Feb 17, 2012
    #6
  7. 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 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.

    >
    > 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 -
     
    Paul Robinson, Feb 17, 2012
    #7
  8. 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<> 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<> 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.
     
    Vacuum Sealed, Feb 20, 2012
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Danny Bowers

    excel spreadsheet has little bits of embedded code in it ? help needed

    Danny Bowers, Oct 3, 2003, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    186
  2. qitrader888

    Need a little help in my macro please

    qitrader888, Feb 26, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    1,215
    qitrader888
    Feb 26, 2004
  3. Stitch45

    A little help with SaveAs Method please

    Stitch45, Apr 29, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    201
    Doug Glancy
    Apr 30, 2005
  4. James8309

    Little help please please!!!

    James8309, Jun 11, 2008, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    125
    James8309
    Jun 12, 2008
  5. James8309

    Little help with filter Please please!

    James8309, Jun 16, 2008, in forum: Microsoft Excel Programming
    Replies:
    9
    Views:
    162
    James8309
    Jun 17, 2008
Loading...

Share This Page