CODEWITHSUNDEEP
excelvba
J-Man
J-Man

Reputation: 11

Faster way to loop through cells with If, ElseIf

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

Answers (2)

Siddharth Rout
Siddharth Rout

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

Kazimierz Jawor
Kazimierz Jawor

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

Related Questions