Help with a loop VBA with an if statement

kixelsid,

A couple questions first.

1. Will the values you are looking for/adding to running total always
be in the same row? For example will you be comparing A3 = date, C3 =
value, and E3 added to running total; then A4, C4, E4; then A5, C5,
E5...etc.

2. Will you be looking for the first occurence of (Ax = date) AND (Cx
= value) and starting your running total from there and going until
the end, OR will your running total only add the value in E at each
occurence of (Ax = date) AND (Cx = value)? How you have your question
written, it appears that it will only add to the running total at each
occurence of (Ax = date) AND (Cx = value).

Conan



"kixelsid" <kixelsid.23r03a_1140808801.2831@excelforum-nospam.com>
wrote in message
news:kixelsid.23r03a_1140808801.2831@excelforum-nospam.com...
>
> I need help with a satement that will verify the date in one
> row(sheet1.A3) and a second value(sheet1.c3) are equal to set
> values.
> If they are both equal i need to start a running total. then display
> that total on a diffrent sheet.
>
> I understand the basics of programing, just not the language that
> excel
> uses. I was going to have the user enter the total number of
> itterations.
>
> I need to know how to increase the row using the loop variable. and
> I
> need to know how to pass data from workbook, to VBA, and back.
>
> So in excel say sheet1.A1 is the total number of times to do the
> loop.
> stuff in { } is what i need help with.
> the IF is writen in what Excel uses for an if statement which i
> believe
> is not what the VBA will use.
>
> num = 0
> For loopx = 3 to Sheet1.A1
> If((AND(Sheet1!A3{this needs to increase with the count of loopx} =
> dateiamlookingfor, Sheet1!C3{this needs to increase with the count
> of
> loopx} = valueiamlookingfor)),num = num + E3{this needs to increase
> with the count of loopx}
> next loopx
> {Print num to a cell on sheet2}
>
> I know its alot to ask, my brain is just dead, and i do not know
> much
> about excel...yet
>
> Thanks
>
>
> --
> kixelsid
> ------------------------------------------------------------------------
> kixelsid's Profile:
> http://www.excelforum.com/member.php...o&userid=31901
> View this thread:
> http://www.excelforum.com/showthread...hreadid=516301
>

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.

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
>

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
>