CODEWITHSUNDEEP
vbaexcelif-statementcountif
Wowdude
Wowdude

Reputation: 129

'Countif' combined with 'If'

I'm looking to use a set variable as the search criterion for a Countif statement to look for "User 1" in "A:A" in sheet "Unallocated". If the Countif comes back with a value of 0, I want to skip running code and instead carry on to the next Countif (there aren't any currently but once I get this logic correct I will use it a few times over).

Here is what I have so far:

Private Sub CommandButton21_Click()

Dim iVal As Integer
Dim User As String
'Dim Wkb As Workbook 'to be used later

User = "User 1"
iVal = Application.WorksheetFunction.CountIf(Worksheets("Unallocated").Range("A2:A"), "User 1")
If iVal = 0 Then GoTo ALabel
Call User1Allo 'macro held in a module
MsgBox ("It Tried to run the macro")
ALabel:
MsgBox ("It Didn't try to run the macro")

'For Each Wkb In Workbooks 'to be used later
'        If Not Wkb.ReadOnly And Windows(Wkb.Name).Visible Then 'to be used later
'            Wkb.Save 'to be used later
'        End If 'to be used later
'    Next 'to be used later


End Sub

Upvotes: 0

Views: 211

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

I see from the comments you fixed the A2:A syntax. Now in order for the code to truly skip the User1Allo module, I would suggest getting rid of the GoTo and the Call and just embed everything in a full If ... Then ... Else block.

See below:

User = "User 1"
iVal = Application.WorksheetFunction.CountIf(Worksheets("Unallocated").Range("A:A"), User)

If iVal = 0 Then
    MsgBox ("It Didn't try to run the macro")
Else
    User1Allo 'macro held in a module
    MsgBox ("It Tried to run the macro")
End If

Upvotes: 1

Related Questions