VBA Countif variable range

[Human_doing]

Hi all,

Can anyone please amend the below code. The objective is that cell E2 contains the result of a COUNTIF of all cells in column B with data i.e. the final output might be:

=COUNTIF(B1:B18,"*")

But there may be more or fewer than 18 rows?

Thanks

    Dim LR As Long
    With ActiveSheet
 
 
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("E2").Formula = "=COUNTIF((B2:B" & LR & " ," * "))"
 
    End With

 

[wigi]

Sub f()
    Range("E2").Formula = "=COUNTA(B2:B" & Range("B" & Rows.Count).End(xlUp).Row & ")"
End Sub

 

[VoG]

Try

.Range("E2").Formula = "=COUNTIF((B2:B" & LR & ",""*""))"

 

[Human_doing]

Thanks for getting back to me but I still get error: application defined or object defined error for both of your suggestions.

Perhaps a paste of my full code would help?

'Download relevant data from SQL
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=xxxx;PWD=xxxx;APP=Microsoft Office 2003;WSID=PCIDxxxx" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_status, AuditData_AuditForm.frm_end_date" & Chr(13) & "" & Chr(10) & "FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm" & Chr(13) & "" & Chr(10) & "ORDER BY AuditData_AuditForm.frm_id" _
        )
        .Name = "Query from Sqlserver2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("C:C").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("A1").Select
 
'Populate E2 with total records    
    Dim LR As Long
    With ActiveSheet
 
 
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("E2").Formula = "=COUNTIF((B2:B" & LR & ",""*""))"
 
    End With

Thanks

 

[wigi]

My code replaces the code you wrote in your opening post of the topic.

Run this code in isolation to convince yourself it works.

Then, run only the first part of the code until that works without issues.

Then, the 2 codes.

Also, avoid selecting. For instance, this is equivalent:

Columns("C:C").NumberFormat = "m/d/yyyy"

 

[Human_doing]

Hi, I do very much appreciate your help but I'm afraid I still get the exact same error, whether running the code in isolation or as part of the main body,

Thanks

 

[wigi]

I'm very soo, but my code works.

In addition, Peter's code is wrong in that there are double brackets after COUNTIF and near the end of the formula.

Although I would not understand why you would want to use that version of the code (or yours) when there are simpler alternatives.