PDA

View Full Version : Test for multiple conditions



austenr
09-26-2006, 08:22 AM
I need to test for three conditions on one row. I need to check if column F is equal to 1,2,3 or 4. Column I = 0.011 and Column G is between 10,000 and 50,000. If all are truethen i want to take certain action like adding other cells to totals I have set up and to counters. I have tried several ways, but so far not much luck. Can anyone provide direction? Thanks

hdpruett@cox
09-26-2006, 08:58 AM
Sub TestConcept()
' F10 contains a 3, G10 contains 20000 and I10 contains .011
' running the sub prints True
Debug.Print Range("F10") Like "[1-4]" And Range("G10") Like "[1-5]0000" And Range("I10") = 0.011
End Sub

mvidas
09-26-2006, 09:03 AM
Austen,

Are you looking for vba to do this or a formula? I'm a little confused about what you're trying to do, is it one specific row or will you be doing this on all rows?
=IF(AND(OR(F2=1,F2=2,F2=3,F2=4),I2=0.011,AND(G2>=10000,G2<=50000)),"all conditions met","all conditions not met")

>"adding other cells to totals i have set up and to counters"
If only in formulas you should be able to do this with sumproduct..

Not sure if that helps you, if not can you explain what you're trying to do a little more?
Matt

austenr
09-26-2006, 09:06 AM
Hi Matt,

I need a macro. When all three conditions are met I need to add other cells to a running total and update some counters. Thanks

Austen

austenr
09-26-2006, 09:09 AM
Sorry hd could not get your routine to work.

mdmackillop
09-26-2006, 09:58 AM
Hi Austen
I'm having trouble comprehending this. Can you post a small sample with some explanation?

austenr
09-26-2006, 10:32 AM
Even a few records zipped exceeds the available file size. I have put more detailed instructions below.

Group the rows into three categories:10,00 to 50,00051,000 to 100,000101,000 and upGroups are driven by the Rate Parm field and the ECRRate Parm 1,2,3,4 and ECR 0.011 would be grouped together that have available balances of 10,000 to 50,000, 51,000 to 100,000, and 101,000 and upI need to do this for every ECR that is different. So the unique identifiers are Rate Parm and ECR which determine the bucket.Rate Parm 5 is by itself, the others are grouped together 1,2,3,4.I know it is confusing but I hope I made it more clear.It is basically a summary report which I will then do calculations on.

mdmackillop
09-26-2006, 11:07 AM
Option Explicit
Sub Test3()
Dim i As Long
i = ActiveCell.Row
Select Case Cells(i, "F")
Case 1 To 4
Select Case Cells(i, "G")
Case 10000 To 50000
If Cells(i, "I") = 0.011 Then
DoSomething i
Else
MsgBox "Failed on I"
End If
Case Else
MsgBox "Failed on G"
End Select
Case Else
MsgBox "Failed on F"
End Select
End Sub
Sub DoSomething(i As Long)
MsgBox "Row " & i & " fits the bill!"
End Sub

mdmackillop
09-26-2006, 11:11 AM
BTW Austen,
If you clear your colour from cells, the file size reduces to 76kb

austenr
09-26-2006, 12:52 PM
Hi Malcomb,

OOPS...Forgot all about that trying in haste to get this done. Thanks that will do what I need with a little modification. One more thing, How can I take a workbook with all rows in it and write them to new sheets in the same workbook. I have criteria that I can use to know when to change to the different sheet. Just need the copy part of the code.

mdmackillop
09-26-2006, 02:07 PM
Why not just copy the sheet and rename it to suit?

austenr
09-26-2006, 04:09 PM
I have criteria that changes. Basically, I have a master sheet that contains the entire data. Say sheet1 contains the master data. In column A I have data, say a number, from 1 to 20. There is never all 20 present. Most times it is around 10 total. So, beginning with A1 create a sheet called "1" and copy all of the rows with "1" in sheet1 Column A to the sheet "1". When the number in column A changes, create a new sheet with the new number and repeat the above until all rows are extracted to new sheets. The goal is to break out the data so all the same numbers in column A rows in sheet1 have their own sheet. HTH

mdmackillop
09-26-2006, 04:56 PM
Option Explicit
Sub Macro1()
Dim rg As Range, i As Long
Application.ScreenUpdating = False
For i = 1 To 20
With Sheets("Sheet1")
.Cells.AutoFilter Field:=1, Criteria1:=i
Set rg = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not rg.Address = "$A$1" Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = i
rg.EntireRow.Copy Sheets(CStr(i)).Cells(1, 1)
End If
.Cells.AutoFilter
.Activate
End With
Next
Application.ScreenUpdating = False
End Sub

austenr
09-26-2006, 07:24 PM
Thank you my friend. I shall give it a go in the morning and post back. Thanks a bunch.

austenr
09-27-2006, 05:42 AM
Thanks Malcomb. Works great as usual. One other question. Is it possible to run a macro on filtered data? If so how?

mdmackillop
09-27-2006, 11:49 AM
Something like

Sub Macro1()
Columns(1).AutoFilter Field:=1, Criteria1:="1"
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
Debug.Print cel
Next
End Sub

austenr
09-27-2006, 12:04 PM
So change the filter criteria to the column that is doing the filtering. What is the Criteria:=1 for?

My filter column is column 9.

mdmackillop
09-27-2006, 12:37 PM
1 is just my filter criteria in my sample worksheet.