PDA

View Full Version : check multiple conditions in a excel sheet through VBA



aravindhan_3
06-20-2009, 11:45 PM
Hi,

I have to check some 25 different conditions on my file like below.

Case 1.
IF values in column D Contains "RENT" then check if
Col O has "Live" &
Col R has "ASAP" &
Col T has "Land" &
Col U has "Pay" or "LandL"
if any of the above conditions is not met then result as "Incorrect" in Column AA

Case 2:
IF values in Column D Contains "RQ" then check if
Col N has "Live" &
Col P has "ASAP"
if any of the above conditions is not met then result as "Incorrect" in Column AA

Case 3:
IF values in Column D Contains "UTI" then check if
Col Y has "Live" &
Col Z has "ASAP"
if any of the above conditions is not met then result as "Incorrect" in Column AA

similarly i have some 25 cases to check, if someone helps me to check the above condition through VBA I can amend the code as per my requirement.

Thanks for your help
Arvind

GTO
06-21-2009, 02:18 AM
Hi Arvind,

I think this could get a bit slow if your range in D Col is awfully long. You could tack in shutting off screen updating and calculation if it seems slow.

That said, using a basic Select Case statement and some IFs should work...

Option Explicit

Sub EX()
Dim rngD As Range, rngCell As Range

With shtData '<--- // I changed and used the codename to a sheet.
'// You could also use the sheet's tab name like:
'// ThisWorkbook.Worksheets("YourSheetsName")
Set rngD = .Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row)

'// Clear the renge of cells in Col AA in case we are re-running with //
'// updated data. //
rngD.Offset(, 23).ClearContents

For Each rngCell In rngD
Select Case rngCell.Value
Case "RENT"
If Not rngCell.Offset(, 11).Value = "Live" _
Or Not rngCell.Offset(, 14).Value = "ASAP" _
Or Not rngCell.Offset(, 16).Value = "Land" _
Or _
(Not rngCell.Offset(, 17).Value = "Pay" _
And Not rngCell.Offset(, 17).Value = "LandL") Then

rngCell.Offset(, 23).Value = "Incorrect"
End If
Case "RQ"
If Not rngCell.Offset(, 10).Value = "Live" _
Or Not rngCell.Offset(, 12).Value = "ASAP" Then

rngCell.Offset(, 23).Value = "Incorrect"
End If
Case "UTI"
If Not rngCell.Offset(, 21).Value = "Live" _
Or Not rngCell.Offset(, 22).Value = "ASAP" Then

rngCell.Offset(, 23).Value = "Incorrect"
End If
End Select
Next
End With
End Sub


Hope this helps,

Mark

Bob Phillips
06-21-2009, 03:22 AM
Const sFormula As String = _
"=IF(OR(AND(D2=""RENT"",O2=""Live"",R2=""ASAP"",T2=""Land"",OR(U2=""Pay"",U2=""LandL""))," & _
"AND(D2=""RQ"",N2=""Live"",P2=""ASAP"")," & _
"AND(D2=""UTI"",Y2=""Live"",Z2=""ASAP"")),"""",""Incorrect"")"
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
With .Range("AA2").Resize(LastRow - 1)

.Formula = sFormula
.Value = .Value
End With
End With

GTO
06-21-2009, 04:52 AM
:omg2: Okay Bob, I suppose that might be the teensiest bit faster...

(For real, like over 4 times faster, and that's just on a small range and sickly laptop!!!)

If I was to follow your lead there, I would try:

Const sFormula As String = _
"=IF(OR(D2=""RENT"",D2=""RQ"",D2=""UTI"")," & _
"IF(OR(AND(D2=""RENT"",O2=""Live"",R2=""ASAP"",T2=""Land"",OR(U2=""Pay"",U2=""LandL""))," & _
"AND(D2=""RQ"",N2=""Live"",P2=""ASAP"")," & _
"AND(D2=""UTI"",Y2=""Live"",Z2=""ASAP"")),"""",""Incorrect""),"""")"


Would that be a decent 'Case Else' or should it be something else?

Mark

aravindhan_3
06-21-2009, 05:51 AM
Hi GTO,

I tried your vba I am getting an error like on line
With ShtData '<--- // I changed and used the codename to a sheet. with the error message Variable not defined
I tried declaring Dim ShtData as worksheets i still get an error.
If I need to run the macro in a an activesheet.. how do i do the changes?

Arvind

GTO
06-21-2009, 06:02 AM
Hi Arvind,

Gosh, I sure would use XLD's! I know the string construction can get a bit "Ouch! My Eyes!" when trying to get quote marks and stuff, but Bob's method is just SO much faster!

Okay, to answer though, just use:

With ActiveSheet


Hope that helps,

Mark

aravindhan_3
06-21-2009, 06:47 AM
Hi,

Thanks for your help, I am not getting the result I think my data is incorrect. I will put all my condition in an excel sheet and attach the sample file on monday.

Thanks once again

aravindhan_3
07-01-2009, 10:59 AM
Hi,

I apologies for not responding for so many days, I had just collected the data, and modified the code as below for one conditions. I also attached a sample file with all the conditions and desired result.

In the below code for condition 1, I dont know where I am going wrong I am not getting any result on my sheet.
Sub EX()
Dim rngD As Range, rngCell As Range
Sheets("SUPPLIER REPORT").Select

With ActiveSheet '<--- // I changed and used the codename to a sheet.
'// You could also use the sheet's tab name like:
'// ThisWorkbook.Worksheets("YourSheetsName")
Set rngD = .Range("E2:E" & .Cells(Rows.Count, 4).End(xlUp).Row)

'// Clear the renge of cells in Col AA in case we are re-running with //
'// updated data. //
rngD.Offset(, 22).ClearContents

For Each rngCell In rngD
Select Case rngCell.Value
' check in E colum if it CONTAINS "RENT"
Case "*RENT*"
If Not rngCell.Offset(, 10).Value = "Live File" _
Or Not rngCell.Offset(, 12).Value = "IMMEDIATE" _
Or Not rngCell.Offset(, 15).Value = "LANDLORD" _
Or Not rngCell.Offset(, 20).Value = "Yes" _
Or Not rngCell.Offset(, 21).Value = "No" _
Or _
(Not rngCell.Offset(, 16).Value = "HZ: Landlord" _
And Not rngCell.Offset(, 17).Value = "HZ: Payee") Then
rngCell.Offset(, 22).Value = "Incorrect"
End If
'Case "RQ"
' If Not rngCell.Offset(, 10).Value = "Live" _
' Or Not rngCell.Offset(, 12).Value = "ASAP" Then

' rngCell.Offset(, 23).Value = "Incorrect"
'End If
'Case "UTI"
' If Not rngCell.Offset(, 21).Value = "Live" _
' Or Not rngCell.Offset(, 22).Value = "ASAP" Then

' rngCell.Offset(, 23).Value = "Incorrect"
'End If
End Select
Next
End With
End Sub

I also tried with XLDs formula but I dont know how to modify the formula to check if E2 contain some word tried with * but didnot work

Thanks for your help and suggestion in advance
Arvind

mdmackillop
07-01-2009, 12:58 PM
A different methodology

Option Explicit
Option Compare Text

Sub Compar()
Dim arr(3, 2)
Dim i As Long

arr(0, 0) = "rent"
arr(0, 1) = "Live FileImmediateLandlordHZ: LandlordYesNo" 'Data string to be compared
arr(0, 2) = "15,17,20,21,25,26" 'Columns containing conditions

arr(1, 0) = "rent"
arr(1, 1) = "Live FileImmediateLandlordHZ: PayeeYesNo"
arr(1, 2) = "15,17,20,21,25,26"

arr(2, 0) = "relo"
arr(2, 1) = "Live FileImmediate"
arr(2, 2) = "15,17"

arr(3, 0) = "CIS"
arr(3, 1) = "CIT"
arr(3, 2) = "15," '<== comma required for single values

For i = 0 To 3
EX arr(i, 0), arr(i, 1), arr(i, 2)
Next

End Sub

Sub EX(Data, Conditions, Cols)
Dim k As Long, Rw As Long
Dim c As Range
Dim a
Dim FirstAddress As String, Cond As String
With Worksheets(1).Columns(5)
Set c = .Find(Data, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Rw = c.Row
If Not Cells(Rw, 27) = "Correct" Or Cells(Rw, 1) = "Incorrect" Then
a = Split(Cols, ",")
For k = 0 To UBound(a)
If a(k) <> "" Then Cond = Cond & Cells(Rw, a(k) * 1)
Next k
If Conditions = Cond Then
Cells(Rw, 27) = "Correct"
Else
Cells(Rw, 27) = "Incorrect"
End If
Cond = ""
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

aravindhan_3
07-06-2009, 01:09 AM
Hi,

Thanks for your help, I works for me I have modified the code as below and its works for me. However I want to do 2 more changes on this

The macro check values in ColE which are fine. I want to add more conditions apart from this.

1. If T contain "More" then check if col O has "ARV" and Column Q has "Later".


2. If Col P contains "Transfer" then check if col V has email address( 123@email.com)


Option Explicit
Option Compare Text

Sub Compar1()
Dim arr(12, 11)
Dim i As Long
Columns("AA:AA").Select
Selection.Clear
Range("AA1").Select
arr(0, 0) = "Rent"
arr(0, 1) = "Live FileImmediateLandlordHZ: LandlordYesNo" 'Data string to be compared
arr(0, 2) = "15,17,20,21,25,26" 'Columns containing conditions

arr(1, 0) = "Rent"
arr(1, 1) = "Live FileImmediateLandlordHZ: PayeeYesNo"
arr(1, 2) = "15,17,20,21,25,26"

arr(2, 0) = "RELO"
arr(2, 1) = "Live FileImmediateYesNoEXEMPT"
arr(2, 2) = "15,17,25,26,23"

arr(3, 0) = "RQ-RELO"
arr(3, 1) = "Live FileImmediateYesNoEXEMPT"
arr(3, 2) = "15,17,25,26,23"

arr(4, 0) = "RELO-RQ"
arr(4, 1) = "Live FileImmediateYesNoEXEMPT"
arr(4, 2) = "15,17,25,26,23"

arr(5, 0) = "RQ"
arr(5, 1) = "Live FileImmediateYesNoEXEMPT"
arr(5, 2) = "15,17,25,26,23"

arr(6, 0) = "RELO"
arr(6, 1) = "Live FileImmediateYesNoZERO RATED"
arr(6, 2) = "15,17,25,26,23"

arr(7, 0) = "RQ-RELO"
arr(7, 1) = "Live FileImmediateYesNoZERO RATED"
arr(7, 2) = "15,17,25,26,23"

arr(8, 0) = "RELO-RQ"
arr(8, 1) = "Live FileImmediateYesNoZERO RATED"
arr(8, 2) = "15,17,25,26,23"

arr(9, 0) = "RQ"
arr(9, 1) = "Live FileImmediateYesNoZERO RATED"
arr(9, 2) = "15,17,25,26,23"

arr(10, 0) = "UTI"
arr(10, 1) = "Live FileImmediate"
arr(10, 2) = "15,17"

arr(11, 0) = "CIS"
arr(11, 1) = "CIT"
arr(11, 2) = "15," '<== comma required for single values

'arr(8, 0) = "DOC"
'arr(8, 1) = "7 Days"
'arr(8, 2) = "17," '<== comma required for single values

arr(12, 0) = "Freight"
arr(12, 1) = "30 Days"
arr(12, 2) = "17," '<== comma required for single values


For i = 0 To 12
EX arr(i, 0), arr(i, 1), arr(i, 2)

Next

Range("AA1").FormulaR1C1 = "QC Comments"
End Sub

Sub EX(Data, Conditions, Cols)
Dim k As Long, Rw As Long
Dim c As Range
Dim a
Dim FirstAddress As String, Cond As String
With Worksheets(1).Columns(5)
Set c = .Find(Data, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Rw = c.Row
If Not Cells(Rw, 27) = "Correct" Or Cells(Rw, 1) = "Incorrect" Then
a = Split(Cols, ",")
For k = 0 To UBound(a)
If a(k) <> "" Then Cond = Cond & Cells(Rw, a(k) * 1)
Next k
If Conditions = Cond Then
Cells(Rw, 27) = "Correct"
Else
Cells(Rw, 27) = "Incorrect"
End If
Cond = ""
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub


Thanks for you help
Arvind

aravindhan_3
01-05-2010, 02:48 AM
The below code is working fine for me. I would need ur help for some changes on this :
Option Explicit
Option Compare Text

Sub Compar()
Dim arr(3, 2)
Dim i As Long

arr(0, 0) = "rent"
arr(0, 1) = "Live FileImmediateLandlordHZ: LandlordYesNo" 'Data string to be compared
arr(0, 2) = "15,17,20,21,25,26" 'Columns containing conditions

arr(1, 0) = "rent"
arr(1, 1) = "Live FileImmediateLandlordHZ: PayeeYesNo"
arr(1, 2) = "15,17,20,21,25,26"

arr(2, 0) = "relo"
arr(2, 1) = "Live FileImmediate"
arr(2, 2) = "15,17"

arr(3, 0) = "CIS"
arr(3, 1) = "CIT"
arr(3, 2) = "15," '<== comma required for single values

For i = 0 To 3
EX arr(i, 0), arr(i, 1), arr(i, 2)
Next

End Sub

Sub EX(Data, Conditions, Cols)
Dim k As Long, Rw As Long
Dim c As Range
Dim a
Dim FirstAddress As String, Cond As String
With Worksheets(1).Columns(5)
Set c = .Find(Data, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Rw = c.Row
If Not Cells(Rw, 27) = "Correct" Or Cells(Rw, 1) = "Incorrect" Then
a = Split(Cols, ",")
For k = 0 To UBound(a)
If a(k) <> "" Then Cond = Cond & Cells(Rw, a(k) * 1)
Next k
If Conditions = Cond Then
Cells(Rw, 27) = "Correct"
Else
Cells(Rw, 27) = "Incorrect"
End If
Cond = ""
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub


The code valides the conditions and input "Correct" or "Incorrect" in Column AA which is fine, is there a way to input this, only in blank cells of col AA: Because I manually input some values in some rows in this column and if I run a macro it overwrites, so Each time I run a macro, the values of correct or incorrect should be filled only in blank cells.
Thanks for your help:

Bob Phillips
01-05-2010, 04:27 AM
Maybe just change this



If Conditions = Cond Then
Cells(Rw, 27) = "Correct"
Else
Cells(Rw, 27) = "Incorrect"
End If


to



If Cells(Rw, 27).Value = "" Then
If Conditions = Cond Then
Cells(Rw, 27) = "Correct"
Else
Cells(Rw, 27) = "Incorrect"
End If
End If