Reputation: 11
I have five columns which could have a value of "True", "False", "Bill-to Not in POVA", or "Logic Code Incorrect" and need to interpret these five columns and return a value in a different column based upon a hierarchy.
So far I've only developed the If/Elseif statements for once cell. I know I can loop through the cells, but this code runs on about 5,000 rows right now and could go up to 15,000 as the months progress.
Does anyone have a suggestion regarding an efficient way to do this? Note that "Rules" refers to a named range which I named in an earlier part of the code.
Sub Finalize_Formulas()
Dim Rules As Range
Set Rules = Range("Rules")
Dim NumRecords As Long
NumRecords = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Range("B" & Rows.Count).End(xlUp).Row
'First, copy and paste special (values only) so that we aren't working with formulas anymore
Cells.Copy
Cells.PasteSpecial xlPasteValues
'Now let's interpret the results in Column F based on Columns G-K (Rules 1-5)
Rules.Select
Rules(1, 1).Select
Rules(1, 1).Activate
If Rules(1, 1).OffSet(0, 1) = "True" And _
Rules(1, 1).OffSet(0, 2) = "True" And _
Rules(1, 1).OffSet(0, 3) = "True" And _
Rules(1, 1).OffSet(0, 4) = "True" And _
Rules(1, 1).OffSet(0, 5) = "True" Then
Rules(1, 1).Value = "True"
ElseIf Rules(1, 1).OffSet(0, 1) = "Bill-to Not in POVA" Or _
Rules(1, 1).OffSet(0, 2) = "Bill-to Not in POVA" Or _
Rules(1, 1).OffSet(0, 3) = "Bill-to Not in POVA" Or _
Rules(1, 1).OffSet(0, 4) = "Bill-to Not in POVA" Or _
Rules(1, 1).OffSet(0, 5) = "Bill-to Not in POVA" Then
Rules(1, 1).Value = "Bill-to Not in POVA"
ElseIf Rules(1, 1).OffSet(0, 1) = "Logic Code Incorrect" Or _
Rules(1, 1).OffSet(0, 2) = "Logic Code Incorrect" Or _
Rules(1, 1).OffSet(0, 3) = "Logic Code Incorrect" Or _
Rules(1, 1).OffSet(0, 4) = "Logic Code Incorrect" Or _
Rules(1, 1).OffSet(0, 5) = "Logic Code Incorrect" Then
Rules(1, 1).Value = "Logic Code Incorrect"
ElseIf Rules(1, 1).OffSet(0, 1) = "False" Or _
Rules(1, 1).OffSet(0, 2) = "False" Or _
Rules(1, 1).OffSet(0, 3) = "False" Or _
Rules(1, 1).OffSet(0, 4) = "False" Or _
Rules(1, 1).OffSet(0, 5) = "False" Then
Rules(1, 1).Value = "False"
Else
End If
End Sub
The main concern I have is how to get the code to perform this check on each row in the range "Rules". The above seems to work fine, but is there an efficient way to either loop through the rows in the range (about 5,000 rows) or somehow to evaluate all at once?
I did find this solution, which isn't exactly what I need, but highlights a potentially faster way to loop through the rows: https://stackoverflow.com/a/8178637/2883304
Upvotes: 0
Views: 707
Reputation: 149335
Building on KazJaw's answer
You don't need IF/ELSE
or loops to achieve what you want. You can use the AUTOFILL
method to achieve what you want.
LOGIC
If say the values are in cell G2:K2
, then what you are actually trying is this formula
=IF(COUNTIF(G2:K2,TRUE)=5,TRUE,IF(COUNTIF(G2:K2,FALSE)=5,FALSE,IF(COUNTIF(G2:K2,"Bill-to Not in POVA")=5,"Bill-to Not in POVA",IF(COUNTIF(G2:K2,"Logic Code Incorrect")=5,"Logic Code Incorrect",""))))
Simply use that in VBA.
Here is an example.
Let's say you have data from G2:K1000
, then simply use this code
Sub Sample()
With ThisWorkbook.Sheets("Sheet1")
.Range("F2:F1000").Formula = "=IF(COUNTIF(G2:K2,TRUE)=5,TRUE,IF(COUNTIF(G2:K2,FALSE)=5" & _
",FALSE,IF(COUNTIF(G2:K2,""Bill-to Not in POVA"")=5,""Bill" & _
"-to Not in POVA"",IF(COUNTIF(G2:K2,""Logic Code Incorrect"")" & _
"=5,""Logic Code Incorrect"",""""))))"
.Range("F2:F1000").Value = .Range("F2:F1000").Value
End With
End Sub
Upvotes: 0
Reputation: 19087
You could try something like this instead your If statement
:
If Application.CountIf(Range(Rules(1, 1).Offset(0,1), Rules(1, 1).Offset(0,5)), "True*") = 5 Then
Rules(1, 1).Value = "True"
ElseIf Application.CountIf(Range(Rules(1, 1).Offset(0,1), Rules(1, 1).Offset(0,5)), "Bill-to Not in POVA") >0 Then
Rules(1, 1).Value = "Bill-to Not in POVA"
ElseIf Application.CountIf(Range(Rules(1, 1).Offset(0,1), Rules(1, 1).Offset(0,5)), "Logic Code Incorrect") >0 Then
Rules(1, 1).Value = "Logic Code Incorrect"
ElseIf Application.CountIf(Range(Rules(1, 1).Offset(0,1), Rules(1, 1).Offset(0,5)), "False*") >0 Then
Rules(1, 1).Value = "False"
Else
End If
However, look into condition of True
and False
- due to my test it's required to add asterisk when checking of CountIf condition
.
Upvotes: 1