+ Reply to Thread
Results 1 to 11 of 11

Help with a loop VBA with an if statement

Hybrid View

kixelsid Help with a loop VBA with an... 02-24-2006, 03:17 PM
Guest Re: Help with a loop VBA with... 02-24-2006, 05:25 PM
kixelsid Thanks for taking a look. ... 02-24-2006, 07:29 PM
Guest Re: Help with a loop VBA with... 02-24-2006, 08:20 PM
Guest Re: Help with a loop VBA with... 02-24-2006, 11:35 PM
Guest Re: Help with a loop VBA with... 02-25-2006, 01:30 AM
kixelsid Many thanks. This was exactly... 02-25-2006, 10:01 AM
kixelsid Conan, 1 more question if... 02-27-2006, 01:36 PM
kixelsid Also, what is the VBA command... 02-27-2006, 01:52 PM
Guest Re: Help with a loop VBA with... 02-27-2006, 02:45 PM
Guest Re: Help with a loop VBA with... 02-28-2006, 03:20 PM
  1. #1
    Registered User
    Join Date
    02-24-2006
    Posts
    7
    Thanks for taking a look.

    I think if I try to explain what i am trying todo both questions will be answered.

    On sheet1 I have things set up so each row is an entry. Each colum of that row is the number of times something was done. Example: Column A is always going to be the date column C will always be and ID number and columns E through BD are the diffrent services. A3 = 02/23/06 C3=991 E3 = 1 AD3 = 5. There may be 1600 rows over a weeks time. I want to pull out to another sheet each day by ID number. Where the trobule is 991 get 10 entrys on 02/23/06 and I need to total column E through BD. Then do the same for 993 on a diffrent sheet.

    Hopefully this helps explain things.

  2. #2
    Conan Kelly
    Guest

    Re: Help with a loop VBA with an if statement

    Unfortunately that didn't help much, but I will try to write the if statement according to how you've worded your question. Check
    back over the weekend, if you can. I will post my results, if I get to it.

    Conan
    "kixelsid" <kixelsid.23rbny_1140823803.515@excelforum-nospam.com> wrote in message
    news:kixelsid.23rbny_1140823803.515@excelforum-nospam.com...
    >
    > Thanks for taking a look.
    >
    > I think if I try to explain what i am trying todo both questions will
    > be answered.
    >
    > On sheet1 I have things set up so each row is an entry. Each colum of
    > that row is the number of times something was done. Example: Column A
    > is always going to be the date column C will always be and ID number
    > and columns E through BD are the diffrent services. A3 = 02/23/06
    > C3=991 E3 = 1 AD3 = 5. There may be 1600 rows over a weeks time. I want
    > to pull out to another sheet each day by ID number. Where the trobule is
    > 991 get 10 entrys on 02/23/06 and I need to total column E through BD.
    > Then do the same for 993 on a diffrent sheet.
    >
    > Hopefully this helps explain things.
    >
    >
    > --
    > kixelsid
    > ------------------------------------------------------------------------
    > kixelsid's Profile: http://www.excelforum.com/member.php...o&userid=31901
    > View this thread: http://www.excelforum.com/showthread...hreadid=516301
    >




  3. #3
    Conan Kelly
    Guest

    Re: Help with a loop VBA with an if statement

    kixelsid,

    Here is something to consider. You might be able to enter formulas on the new sheets that will automatically calculate the values
    that you want.

    If each sheet will have totals for one ID over a time series, the try this. On Sheet 2, enter the ID # in cell A1 (I'll use your
    example: 991). In Column A, enter the time series (I'm assuming that your time series is days). For example in cells A3:A10 enter
    2/19/2006, 2/20/2006, 2/21/2006, 2/22/2006, 2/23/2006, 2/24/2006, and 2/25/2006. Now in cell B3 enter the formula
    "=SUMPRODUCT((Sheet1!$A$3:$A$1600=$A3)*(Sheet1!$C$3:$C$1600=$A$1)*(Sheet1!E$3:E$1600))". This formula will sum all the numbers in
    column E on Sheet1 for whatever ID is entered into A1 (991) for whatever date is in the same row of column A (2/19/2006). I think I
    have all of the absolute/relative cell references setup correctly in order to copy this formula down and accross (that is if each
    column (E:BD) needs to be totaled individually). If all columns need to be totaled together into one cell for each day, then change
    the last element "(Sheet1!E$3:E$1600)" to "(Sheet1!$E$3:$BD$1600)".

    Now if you add new data to this file every day (let say that today you are adding 75 rows of new data), go to the last row of data
    (1600 in the example above) and insert 75 blank rows. Now you will have your last row of data on row 1675, 75 blank rows from
    1599:1674, and the rest of your old data in rows 3:1599. By inserting these rows this way, Excel will automaticall adjust all of
    the references in the above formula so the last row in each range will be 1675. (Actually you can insert these 75 rows anywhere in
    the range of data: (3:1600). You do not have to do it at the last row) Now you can paste the 75 new rows of data in these blank
    rows, or paste all of the data (1673 (1675 - the 2 rows at the top that have lables and other info) rows of data, new & old) in the
    A3 over the top of the old data. You can sort the data any way that you want or leave it unsorted. The SUMPRODUCT formulas should
    recalculate all the new data accurately.

    If this will not work for you, I still plan on creating that if...then...else code with the loop. I will post that later.

    HTH,

    Conan




    "kixelsid" <kixelsid.23rbny_1140823803.515@excelforum-nospam.com> wrote in message
    news:kixelsid.23rbny_1140823803.515@excelforum-nospam.com...
    >
    > Thanks for taking a look.
    >
    > I think if I try to explain what i am trying todo both questions will
    > be answered.
    >
    > On sheet1 I have things set up so each row is an entry. Each colum of
    > that row is the number of times something was done. Example: Column A
    > is always going to be the date column C will always be and ID number
    > and columns E through BD are the diffrent services. A3 = 02/23/06
    > C3=991 E3 = 1 AD3 = 5. There may be 1600 rows over a weeks time. I want
    > to pull out to another sheet each day by ID number. Where the trobule is
    > 991 get 10 entrys on 02/23/06 and I need to total column E through BD.
    > Then do the same for 993 on a diffrent sheet.
    >
    > Hopefully this helps explain things.
    >
    >
    > --
    > kixelsid
    > ------------------------------------------------------------------------
    > kixelsid's Profile: http://www.excelforum.com/member.php...o&userid=31901
    > View this thread: http://www.excelforum.com/showthread...hreadid=516301
    >




+ 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