Help with a loop VBA with an if statement

[kixelsid]

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

 

[Conan Kelly]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[kixelsid]

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.

 

[Conan Kelly]

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

 

[Conan Kelly]

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

 

[Conan Kelly]

kixelsid,

Here is what I came up with. Keep in mind that this code has not been tested, so I hope it works.

Also, I have alot of comments in this code. Some of it is just notes for you, others are actual lines of code commented out. Just
incase you didn't already know this, an apostrophe (') will comment out a line (or anything to the right of the '--good for putting
notes after executable code on the same line). Since you are new to Excel/VBA, read the comments before you delete them. They may
help you with picking up the VBA language faster. The comments show up in my VBA editor as green text. They probably will be green
in yours, but they could be a different color.

To get this code into your spreadsheet, open the VBA Editor, right click your spreadsheet file, click Insert?, click Module. Then
double click the new module ("Module1" if the file has no other modules) to get the blank code window for the new module. Now copy
all of the code below (from Option Explicit to End Sub) and paste it into this blank code window. Notice that I included the Option
Explicit statement at the top, above the Sub routine. This is not necessary, but I highly recomend it. This causes the compiler to
check, before the code executes, to make sure that any variable in the code is declared first. If it is not declared, the code will
not execute and you will get an error message telling you to declare the variable. If you misspell a variable in your code, the
compiler will catch it before it tries to execute. If you leave the Option Explicit statement out, when you run the code, the
compiler will automatically create a new variable for the one that was misspelled. Now you will think that you are using one
variable throughout the code when actually there are 2 different variables and this will cause errors and bugs. (Check this site
out for more info on Option Explicit: http://www.cpearson.com/excel/DeclaringVariables.htm).

There is something that I've left out of this code and that is error handling. And it's not because I'm being rude, it is because I
have not yet mastered error handling. It really should be in here in case something trip it up, so if anyone has any suggestions
for error handling, please, all is welcomed.

I hope this helps. Please let me know if this works. If it doesn't, I will work with you to see if we can get it to. Also let me
know if you have any other questions.

Conan



Option Explicit


Sub IfThenElseLoop()

'Declare variables

'Use only one of the following running total variabls depending
'on what data type you need to keep a running total of.
'Delete or comment out the others.
Dim pintRunningTotal As Integer
' Dim plngRunningTotal As Long
' Dim psngRunningTotal As Single
' Dim pdblRunningTotal As Double

Dim pintLoopCounter As Integer
Dim pdteDateImLookingFor As Date
Dim pintValueImLookingFor As Integer 'or Long, Single, or Double (same as above)
Dim pshtSheet1 As Worksheet
Dim pshtSheet2 As Worksheet


'Initializing variables
pintRunningTotal = 0
pintLoopCounter = 3
Set pshtSheet1 = ActiveWorkbook.Sheets("Sheet1")
Set pshtSheet2 = ActiveWorkbook.Sheets("Sheet2")

'This equals 2/24/06--DateSerial(Year as integer,Month as Integer,Day as Integer)
pdteDateImLookingFor = DateSerial(2006, 2, 24)
'If you enter the date you are looking for in cell A2 on Sheet1...
'...here is another option for initializing this variable
' pdteDateImLookingFor = pshtSheet1.Range("A2").Value

pintValueImLookingFor = 991
'If you enter the value you are looking for in cell C2 on Sheet1...
' pintValueImLookingFor = pshtSheet1.Range("C2").Value

'***Remember, if you choose the optional variable initializations for the Date & Value variables***'
'***delete or comment out the other one***'



'Start of loop
For pintLoopCounter = 3 To pshtSheet1.Range("A1").Value

'Start of IF statement testing the 2 logicals
If pshtSheet1.Cells(pintLoopCounter, 1).Value = pdteDateImLookingFor And _
pshtSheet1.Cells(pintLoopCounter, 3).Value = pintValueImLookingFor Then
'***Notice the 2 lines above. The space and underscore (" _") at the end of the first line...
'***...followed by a carriage return is a way of continuing the same line of code on a new line
'***This is done just for readability. This would work just the same if you put both lines on one...
'***...and deleted the " _" & carriage return. Personally, I prefer it all being on one line,
'***but when you are emailing code, the email programs can wrap the code onto new lines making it...
'***very dificult to read.

'The Cells property used above is what causes the cell to move down a row with each time throught the loop.
'The syntax is Cells(Row as integer, Column as integer). By using pintLoopCounter in position of Row,...
'...you cause the cell in the row equal to the value of pintLoopCounter and in specified column to be...
'...referenced. Remember, Column is an integer. So Column A would be 1, B = 2, C = 3...etc.

pintRunningTotal = pintRunningTotal + pshtSheet1.Cells(pintLoopCounter, 5).Value

End If

Next pintLoopCounter

'Enter the number stored in pintRunningTotal into cell E1 on Sheet2
pshtSheet2.Range("E1").Value = pintRunningTotal

End Sub

 

[kixelsid]

Many thanks. This was exactly what I was looking for. Thanks again!

 

[kixelsid]

Conan,

1 more question if i may. Your script worked perfect all i had to d
was copy/paste & rename it several times for each cell. Now, m
question is since these are subs, can I make 1 macro that runs all th
macros at once

 

[kixelsid]

Also, what is the VBA command to use the last row on sheet1 as the to o
the loop.

For pintLoopCounter = 3 To LastRowOnSheet

 

[Dave Peterson]

I like to pick out a column that I know has data on it when that row is
used--say column X.

with worksheets("sheet1")
lastrowonsheet1 = .cells(.rows.count,"X").end(xlup).row
end with

 

[Conan Kelly]

kixelsid,

Sorry for not getting back to you sooner.

Here is what you can do (using the code I provided as an example):



Sub MasterMacro() 'Or you can give it a more meaningful name than "MasterMacro"
IfThenElseLoop 'If you changed the name of it, use that instead of "IfThenElseLoop"
IfThenElseLoop2 'Or whatever name you gave them when you copied them.
IfThenElseLoop3
IfThenElseLoop4
End Sub



Hopefully that will work for you. Let me know.

Conan