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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
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:
    329
  2. qitrader888

    Need a little help in my macro please

    qitrader888, Feb 26, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    1,452
    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:
    329
    Doug Glancy
    Apr 30, 2005
  4. Ashnook

    A little help with cell reference please

    Ashnook, Mar 11, 2006, in forum: Microsoft Excel Programming
    Replies:
    14
    Views:
    377
    Ashnook
    Mar 11, 2006
  5. sungen99

    A little help with some code please.

    sungen99, Apr 28, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    248
  6. Guest

    Creating Custom Function - A Little Help Please!

    Guest, Aug 16, 2006, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    224
    Niek Otten
    Aug 17, 2006
  7. James8309

    Little help please please!!!

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

    Little help with filter Please please!

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