VBA - Countif with the * Wildcard

[Hunk1]

Hello there, i am pretty much a VBA noob, i am trying to learn every day but the syntax is kicking my ***.

I need a VBA code that counts how many names there is, and the non-VBA formula has been unreliable because somehow =Countif(B:B,"*") counts spaces as 0, so when you subtract the instances, the numbers don't match. so i tried to make a VBA code that would update the formula to =countif("B12:B" & lastrow &, "*") but i am getting a lot of errors, and when i fix the error, it causes another one.

Z10 is the location where the result should remain and it's set for a message box to a prompt.

Any help is apreciated, Thanks all.

inb4: the code is a mess of stuff i could find googling it, however, none managed to work when mixed duo to syntax and a bunch of errors i don't know how to fix, current list has 530 entries.

Private Sub CommandButton1_Click()
'forces the countif formula to self update to last row

Dim rngCount As Range
Dim rowlast As Long
Dim conj As Variant
Dim var(1 To 4) As String

Set rngCount = Range("B12" & rowlast)
Set Value = Range("Z10").CountIf(Range("B12:B" & rowlast), "" * "")
rowlast = .Range("B" & .Row.Count).End(xlUp).Row


var(1) = "*vazi*"
var(2) = "bolsa familia"
var(3) = "nome"
var(4) = "ausente"

    Sheets("Folha de Assinatura").Select
    Range("Z10").Select
         
    With Sheets("Folha de Assinatura")
rowlast = .Range("B" & .Rows.Count).End(xlUp).Row
With .Range("B12:B" & rowlast)
    .Formula = Range("Z10").Select
valor = .Range("Z10").CountIf(Range("B12:B" & rowlast), "" * "")
.Value = .Value
'resultado = .Formula = "=COUNTIFS('Folha de Assinatura'!RC[-24]:R[" & rowlast & "]C[-24],{""*""})"
MsgBox "Total Atualizado, o total de individuos é " & valor & " Individuos.", _
vbInformation, "Total de Individuos Contados e Cadastrados"

End With
End With


End Sub

 

[DanteAmor]

Hi and welcome to MrExcel board!

If you want the value:

Sub CommandButton1_Click()
  With Sheets("Folha de Assinatura")
    .Range("Z10").Value = WorksheetFunction.CountIf(.Range("B12", .Range("B" & Rows.Count).End(3)), "<>")
  End With
End Sub

If you want the formula:

Sub CommandButton2_Click()
  With Sheets("Folha de Assinatura")
    .Range("Z10").Formula = "=COUNTIF('" & .Name & "'!B12:B" & .Range("B" & Rows.Count).End(3).Row & ",""<>"")"
  End With
End Sub

 

[Hunk1]

Lovely, the first one worked flawlessly, how do i define the result on the message box?

like on my early code i defined

Result = .Formula

, in this case, i have a preset formula to remove what i have which is

=Sum(Z10-AA10-AB10-AC10-AE10)

which results in the number i need. How can i integrate them?

 

[DanteAmor]

how do i define the result on the message box?

which results in the number i need. How can i integrate them?

I don't know what you mean by that.
But check the following if it helps you.

Sub CommandButton1_Click()
  Dim result As Double, secondresult As Double
  
  With Sheets("Folha de Assinatura")
    result = WorksheetFunction.CountIf(.Range("B12", .Range("B" & Rows.Count).End(3)), "<>")
    .Range("Z10").Value = result
    MsgBox result
    secondresult = WorksheetFunction.Sum(.Range("Z10") - .Range("AA10") - .Range("AB10") - .Range("AC10") - .Range("AE10"))
    MsgBox secondresult
  End With
End Sub

 

[Hunk1]

Lovely, i managed to make a code with what you suggested into what i need. Thank you very much!!!

Private Sub CommandButton1_Click()
'forces the countif formula to self update to last row
Dim r As Double


With Sheets("Folha de Assinatura")
    .Range("Z10").Value = WorksheetFunction.CountIf(.Range("B12", .Range("B" & Rows.Count).End(3)), "<>")

r = WorksheetFunction.Sum(.Range("Z10") - .Range("AA10") - .Range("AB10") - .Range("AC10") - .Range("AE10"))
  

MsgBox "você tem " & r & " individuos cadastrados.", vbInformation, "Resultado da quantidade de individuos cadastrados"

End With

End Sub