+ Reply to Thread
Results 1 to 4 of 4

Enter Formula via VBA

  1. #1
    Bobby
    Guest

    Enter Formula via VBA

    Greetings
    I have a file that is imported daily and it varies as to the number of lines
    in the file each day. Using a macro I woud like find the next available row
    and enter the following formula in ColH:ColAE (24 Cols)
    =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
    ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
    The example file had 66 rows of data and this is the part that could change
    each day.
    Thanks


  2. #2
    Tom Ogilvy
    Guest

    Re: Enter Formula via VBA

    set rng = cells(rows.count,"H").End(xlup)(2)
    rng.Resize(1,24).formulaR1C1 = "=Sumproduct(--(R3C5:R[-1]C5=""Cat"")," & _
    "--(R3C6:R[-1]C6=""Bird""),--(R3C3:R[-1]C3<>""Black"")," & _
    "--(R3C3:R[-1]C3<>""White""),R3C:R[-1]C)"

    an alternative

    set rng = cells(rows.count,"H").End(xlup)(2)
    s = "=SUMPRODUCT(--($F$3:$F$66=""Cat""),--($G$3:$G$66" & _
    "=""Bird""),--($C$3:$C$66<>""Black"")," & _
    "--($C$3:$C$66<>""White""),H3:H66)"
    s = Replace(s,"66",rng.offset(-1,0).row)
    rng.Resize(1,24).Formula = s
    --
    Regards,
    Tom Ogilvy


    "Bobby" <Bobby@discussions.microsoft.com> wrote in message
    news:9B27F31D-3865-479F-917D-E36123BAFDEE@microsoft.com...
    > Greetings
    > I have a file that is imported daily and it varies as to the number of

    lines
    > in the file each day. Using a macro I woud like find the next available

    row
    > and enter the following formula in ColH:ColAE (24 Cols)
    > =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
    > ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
    > The example file had 66 rows of data and this is the part that could

    change
    > each day.
    > Thanks
    >




  3. #3
    Bill Martin
    Guest

    Re: Enter Formula via VBA

    Bobby wrote:
    > Greetings
    > I have a file that is imported daily and it varies as to the number of lines
    > in the file each day. Using a macro I woud like find the next available row
    > and enter the following formula in ColH:ColAE (24 Cols)
    > =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
    > ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
    > The example file had 66 rows of data and this is the part that could change
    > each day.
    > Thanks
    >

    --------------------

    Tom gave you the "right" answer. Here's a cheezier one which is perhaps easier
    to understand:

    dim LastRow as Long
    dim ColIndex as Long
    LastRow = Range("H1").end(xlDown).row
    for ColIndex = 8 to 31
    cells(LastRow,ColIndex) = "=SUMPRODUCT(...etc...."
    next ColIndex

    Bill


  4. #4
    Bill Martin
    Guest

    Re: Enter Formula via VBA

    Bobby wrote:
    > Greetings
    > I have a file that is imported daily and it varies as to the number of lines
    > in the file each day. Using a macro I woud like find the next available row
    > and enter the following formula in ColH:ColAE (24 Cols)
    > =SUMPRODUCT(--($F$3:$F$66="Cat"),--($G$3:$G$66="Bird"),--
    > ($C$3:$C$66<>"Black"),--($C$3:$C$66<>"White"),H3:H66)
    > The example file had 66 rows of data and this is the part that could change
    > each day.
    > Thanks
    >

    --------------------

    Tom gave you the "right" answer. Here's a cheezier one which is perhaps easier
    to understand:

    dim LastRow as Long
    dim ColIndex as Long
    LastRow = Range("H1").end(xlDown).row
    for ColIndex = 8 to 31
    cells(LastRow + 1,ColIndex) = "=SUMPRODUCT(...etc...."
    next ColIndex

    Bill (Small change edited into code)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1