Arrays, Strings, and Loops, oh my!

[Dr. Demento]

I'm attempting to use VBA in place of a nested-If formula (see end of post for original formula that works); I'm comparing two columns and depending on their values, assign a test outcome (four outcomes: true|false + positive|negative). I'm attempting to use CASE instead of IF, but I'm running into issues because some of my conditions use NOT. Additionally, I'm testing strings in an array, so trying to use a function to test the absence of a string value isn't working too well either.

I'd rather not use a UDF, as the file is already really large, but instead want to evaluate the the conditions and simply write the value to a column. I'm also looking to avoid loops, as I'm trying to learn efficient coding. I'm not married to using CASE, but I thought it would be more efficient than IF.

As an aside - what's the syntax for NOT X NOR Y - is it:
NOT(OR(X, Y)) or
OR(NOT(X), NOT(Y))

Thanks much, everyone.

Option Explicit

Sub AssignSensSpec()

Dim sht As Worksheet
Dim arrTest As Variant
Dim arrGold As Variant
Dim arrSn_Sp As Variant
Dim strTest As String
Dim cntr As Integer
Dim lrow As Integer

Set sht = ThisWorkbook.Sheets("Data")
Set arrTest = sht.Range("J2:J6950")
Set arrGold = sht.Range("N2:N6950")

'   ~~ resize final array
  With sht
    .Activate
    lrow = .Cells(Rows.Count, 1).End(xlUp).Row
    If lrow = 2 Then
      Application.ScreenUpdating = True
      MsgBox ("Sheet Data does not contain any raw data - macro terminated!")
      Exit Sub
    End If
    arrSn_Sp = sht.Range("A2").CurrentRegion.Resize(, 1)
  End With
  
For cntr = 1 To lrow
  Select Case arrSn_Sp(cntr, 1)
    Case IsInArray(arrGold, Not ("S")) = True
      Case (IsInArray(arrTest(cntr), Not ("SP")) = True) Or (IsInArray(arrTest(cntr), Not ("S")) = True)
        arrSn_Sp(cntr) = "TN"
      Case (IsInArray(arrTest(cntr), ("SP")) = True) Or (IsInArray(arrTest(cntr), ("S")) = True)
        arrSn_Sp(cntr) = "FP"
 
  Case IsInArray(arrGold(cntr), "S")
    Case IsInArray(arrTest(cntr), "S") Or IsInArray(arrTest(cntr), "SP")
      arrSn_Sp(cntr) = "TP"
    Case IsInArray(arrTest(cntr), Not ("SP")) Or IsInArray(arrTest(cntr), Not ("S"))
      arrSn_Sp(cntr) = "FN"
  End Select
Next cntr


  sht.Range("AK2:AK6950") = arrSn_Sp
  
End Sub

 
Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
' checks if valueToFind is found in array, no loop!
' http://www.jpsoftwaretech.com/finding-values-in-an-array-without-looping/

  IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
End Function

Here is the original, working formula:
Note: J2 = arrTest || N2 = arrGold
=IF(AND(OR($J2="SP"|$J2="S")|$N2="S")|"TP"|
IF(AND(OR($J2="SP"|$J2="S")|OR($N2="A"|$N2="H"|$N2="N"|$N2="U"))|"FP"|
IF(AND(NOT(OR($J2="SP"|$J2="S"))|$N2="S")|"FN"|
IF(AND(NOT(OR($J2="SP"|$J2="S"))|NOT(OR($N2="S")))|"TN"))))

 

[RickXL]

Hi,

You could just try out your formulas:

Excel 2013
	L	M	N	O
1	X	Y	r1	r2
2	0	0	TRUE	TRUE
3	0	1	FALSE	TRUE
4	1	0	FALSE	TRUE
5	1	1	FALSE	FALSE
Sheet5

Cell Formulas
Range	Formula
N2	=NOT(OR(L2,M2))
N3	=NOT(OR(L3,M3))
N4	=NOT(OR(L4,M4))
N5	=NOT(OR(L5,M5))
O2	=OR(NOT(L2),NOT(M2))
O3	=OR(NOT(L3),NOT(M3))
O4	=OR(NOT(L4),NOT(M4))
O5	=OR(NOT(L5),NOT(M5))

I looked at the logic of your If Statements and it did not seem to fall out easily either way. Select Case statements have the facility to list values in a line but I don't think it helps in this case. I prefer to keep Select Case for straight multiple choice situations like when you display a menu with many options and you need to execute different code for each one.

For everything else I use If statements. That tells me that this is more complicated than multiple choice. However I did it both ways for comparison.

The program flow looks like this:

The If Statements come out like this:

Option Explicit

Sub AssignSensSpec()

    Dim T As String
    Dim G As String
    Dim Ans As String

    Dim sht As Worksheet
    Dim arrTest As Variant
    Dim arrGold As Variant
    Dim arrSn_Sp As Variant
    Dim cntr As Integer
    Dim nrow As Integer
    
    Set sht = ThisWorkbook.Sheets("Data")

    With sht
        .Activate
        nrow = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
        If nrow = 1 Then
            Application.ScreenUpdating = True
            MsgBox ("Sheet Data does not contain any raw data - macro terminated!")
            Exit Sub
        End If
        arrTest = .Range("J2").Resize(nrow)
        arrGold = .Range("N2").Resize(nrow)
        arrSn_Sp = .Range("A2").Resize(nrow)
    End With
      
    For cntr = 1 To nrow
        T = arrTest(cntr, 1)
        G = arrGold(cntr, 1)
        If T = "SP" Or T = "S" Then
            If G = "S" Then
                Ans = "TP"
            Else
                If G = "A" Or G = "H" Or G = "N" Or G = "U" Then Ans = "FP" Else Ans = False
            End If
        Else
            If G = "S" Then Ans = "FN" Else Ans = "TN"
        End If
        arrSn_Sp(cntr, 1) = Ans
    Next
    
    sht.Range("AK2").Resize(nrow) = arrSn_Sp
    
End Sub

and the Select Case version looked like this:

Option Explicit

Sub AssignSensSpec()

    Dim T As String
    Dim G As String
    Dim Ans As String

    Dim sht As Worksheet
    Dim arrTest As Variant
    Dim arrGold As Variant
    Dim arrSn_Sp As Variant
    Dim cntr As Integer
    Dim nrow As Integer

    Set sht = ThisWorkbook.Sheets("Data")

    With sht
        .Activate
        nrow = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
        If nrow = 1 Then
            Application.ScreenUpdating = True
            MsgBox ("Sheet Data does not contain any raw data - macro terminated!")
            Exit Sub
        End If
        arrTest = .Range("J2").Resize(nrow)
        arrGold = .Range("N2").Resize(nrow)
        arrSn_Sp = .Range("A2").Resize(nrow)
    End With
      
    For cntr = 1 To nrow
        T = arrTest(cntr, 1)
        G = arrGold(cntr, 1)
        Select Case T
            Case "SP", "S"
                Select Case G
                    Case "S": Ans = "TP"
                    Case "A", "H", "N", "U": Ans = "FP"
                    Case Else: Ans = False
                End Select
            Case Else
                If G = "S" Then Ans = "FN" Else Ans = "TN"
        End Select
        arrSn_Sp(cntr, 1) = Ans
    Next
    
    sht.Range("AK2").Resize(nrow) = arrSn_Sp
    
End Sub

By the way, I took the liberty of re-arranging your code slightly. I hope it still works as intended.
I created the variables T, G and Ans to make things less cluttered and I did not read in the arrays until I knew there was some data. All the array sizes are the same and are based on the contents of column 1.

Regards,

 

[Dr. Demento]

You, sir, are a rockstar!!! Thanks so much. I especially like the logic diagram - I'll have to implement that for other projects, as it certainly clarifies flow and outcomes.

One question. When you say:

All the array sizes are the same and are based on the contents of column 1.

Do you mean column 1 = A:A or merely the first column compared (in this case J:J)? I presume the latter but wanted to clarify.

Thanks again. Saved me tons of head-pounding

 

[RickXL]

Hi,

I retained this:

nrow = .Cells(.Rows.Count, 1).End(xlUp).Row - 1

OK, it has slight changes, but the column is the same, column A.

If you wanted to make it column J instead you could use:

nrow = .Cells(.Rows.Count, "J").End(xlUp).Row - 1