VBA Code for 'IS EMPTY'

[ddub25]

I have the below code but would like to add another condition - that if, Range("O27:O142"), IS EMPTY.

I have tried, Range("O27:O142"), "" but it doesn't seem to work.

n = Application.WorksheetFunction.CountIfs(Range("B27:B142"), "Strength", Range("K27:K142"), "U-Pu-1", Range("L27:L142"), 1, Range("M27:M142"), "H")

Can anyone help?

 

[Jeffrey Mahoney]

Use COUNTA as a worksheetfunction
IF Application.WorksheetFunction.COUNTA(Range("O27:O142")) = 0 then
Bla Bla
End IF

 

[ddub25]

So, if I changed the 'n = Application.WorksheetFunction.CountIfs' to 'COUNTA' for that line and added the ', Range("O27:O142") = 0' on the end, would that work? (as below)

n = Application.WorksheetFunction.COUNTA((Range("B27:B142"), "Strength", Range("K27:K142"), "U-Pu-1", Range("L27:L142"), 1, Range("M27:M142"), "H", Range("O27:O142") = 0)

 

[Jeffrey Mahoney]

COUNTA is going to test if the range is empty. I thought that's what you wanted. In your COUNTIFS formula you could add the Range("O27:O142") and test for ""

n = Application.WorksheetFunction.COUNIFS((Range("B27:B142"), "Strength", Range("K27:K142"), "U-Pu-1", Range("L27:L142"), 1, Range("M27:M142"), "H", Range("O27:O142") = "")

 

[ddub25]

I have tried to add the Range("O27:O142") and test for "", but as soon as I enter it I get a 'Compile Error: Expected Expression' error and the code turns red. (Note: O27:O142 has been named "Active"). Any suggestions as to why it fails?

This is the line of code as it was before and it works fine:

n = Application.WorksheetFunction.CountIfs(Range("Category"), "Strength", Range("Type"), "U-Pu-2", Range("Level"), 2, Range("Home"), "H")

This is the updated line of code that has a Compile Error and turns red:

n = Application.WorksheetFunction.CountIfs(Range("Category"), "Strength", Range("Type"), "U-Pu-2", Range("Level"), 2, Range("Home"), "H", Range("Active"), = "")

Here is the entire Sub:

Sub STR_UPu2_2_H()

    ' Declare variables
    Dim i As Long, j As Long, n As Long
    Dim arr() As Variant, dict As Object
    
    ' Initialize dictionary object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Get total number of rows that meet the given conditions
    n = Application.WorksheetFunction.CountIfs(Range("Category"), "Strength", Range("Type"), "U-Pu-2", Range("Level"), 2, Range("Home"), "H", Range("Active"), = "")
    
    ' Resize array to hold the random numbers
    On Error Resume Next
    ReDim arr(1 To n, 1 To 1)
    On Error GoTo 0
    
    ' Generate unique random numbers
    For i = 1 To n
        Do
            j = Int(Rnd() * n) + 1
        Loop While dict.Exists(j)
        dict(j) = True
        arr(i, 1) = j
    Next i
    
    ' Write the random numbers to the range P27:P142
    j = 1
    For i = 27 To 142
        If Range("B" & i) = "Strength" And Range("K" & i) = "U-Pu-2" And Range("L" & i) = 2 And Range("M" & i) = "H" And Range("O" & i) = "" Then
            Range("P" & i).Value = arr(j, 1)
            j = j + 1
        End If
    Next i
    
    ' Cleanup
    Set dict = Nothing
  
End Sub