Apply worksheet function in module

V

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
382
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Guys,
I want to apply the below function in a vba module. Please help how it should be. Thanks

Count the total number of blank cells, "AL" and "VL" from cells 3 to end of last row in a column
Application.WorksheetFunction.If(Sum(CountIfs(Range(vRngCol.Cells(3), vRngCol.Cells(vLastRow)), "", "AL", "VL")) > 2, "True", "False") = True

Debug finds error in IF(SUM(COUNTIFS .


VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range
    Dim vLastRow As Long
    Dim vRngCol As Range
    
    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            If IsWeekend(vRngCol.Cells(1)) = False Then
            
                With Columns(vRngCol.Column)
                'Count total number of blank cells, "AL" and "VL" from the 3rd row till lastrow in a column
                Dim LvTtl As Boolean
                LvTtl = Application.WorksheetFunction.If(Sum(CountIfs(Range(vRngCol.Cells(3), vRngCol.Cells(vLastRow)), "", "AL", "VL")) > 2, "True", "False") = True
                   If LvTtl = True Then
                      vRngCol.Cells(2).Font.Size = 18
                End With
                
             Else
                      vRngCol.Cells(2).Font.Size = 12
                              
            End If
  
        Next vRngCol
    End With
    
 End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Maybe using Evaluate - something like this

VBA Code: 
If Evaluate("=SUM(COUNTIF(" & Range(vRngCol.Cells(3), vRngCol.Cells(vlastrow)).Address _
    & ",{"""";""AL"";""VL""}))") > 2 Then
    MsgBox "Ok"
    'do something
End If

M.
 
Upvote 0
Maybe you can use "CountBlank" function...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range
    Dim vLastRow As Long
    Dim vRngCol As Range
    
    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        LvTtl = Application.CountBlank(.Range("AL3:AL" & vLastRow)) _
              + Application.CountBlank(.Range("VL3:VL" & vLastRow))
        For Each vRngCol In vRng.Columns
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With
    
 End Sub
 
Upvote 0
EXCEL MAX said:
Maybe you can use "CountBlank" function...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range
    Dim vLastRow As Long
    Dim vRngCol As Range
   
    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        LvTtl = Application.CountBlank(.Range("AL3:AL" & vLastRow)) _
              + Application.CountBlank(.Range("VL3:VL" & vLastRow))
        For Each vRngCol In vRng.Columns
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With
   
 End Sub
Click to expand...
Hi EXCEL MAX,
Please note 'AL' and 'VL' are abbrev data which I input into each column of the range. I need to count these and the blank cells. I tried to modify yours but having error in the equation.

VBA Code: 
Sub MGetAbsence15()

    Dim vRng As Range
    Dim vLastRow As Long
    Dim vRngCol As Range
    
    With ThisWorkbook.ActiveSheet
      For Each vRngCol In vRng.Columns
      
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        LvTtl = Application.CountBlank(Range(.Cells(3),.Cells(vLastRow)) _
              + Application.CountIf(Range(.Cells(3), .Cells(vLastRow), "AL") _
              + Application.CountIf(Range(.Cells(3), .Cells(vLastRow), "VL")
              
        If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With
    
 End Sub
 

Attachments

  • errorm.png
    errorm.png
    68.1 KB · Views: 9
Upvote 0
If you want to loop through all columns from C to AL and
count the number of cells that contain "AL" or "VL" or they are blank you can try this...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range, vRng2 As Range
    Dim vLastRow As Long
    Dim vRngCol As Range

    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            Set vRng2 = Range(Cells(3, vRngCol.Column), _
                              Cells(vLastRow, vRngCol.Column))
            LvTtl = Application.CountBlank(vRng2) _
                  + Application.CountIf(vRng2, "AL") _
                  + Application.CountIf(vRng2, "VL")
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With

 End Sub
 
Upvote 0
Solution
Marcelo Branco said:
Maybe using Evaluate - something like this

VBA Code: 
If Evaluate("=SUM(COUNTIF(" & Range(vRngCol.Cells(3), vRngCol.Cells(vlastrow)).Address _
    & ",{"""";""AL"";""VL""}))") > 2 Then
    MsgBox "Ok"
    'do something
End If

M.
Click to expand...
Thanks Marcelo Branco, This works. I will use this as a guide to other sheet functions. Thanks !
 
Upvote 0
EXCEL MAX said:
If you want to loop through all columns from C to AL and
count the number of cells that contain "AL" or "VL" or they are blank you can try this...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range, vRng2 As Range
    Dim vLastRow As Long
    Dim vRngCol As Range

    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            Set vRng2 = Range(Cells(3, vRngCol.Column), _
                              Cells(vLastRow, vRngCol.Column))
            LvTtl = Application.CountBlank(vRng2) _
                  + Application.CountIf(vRng2, "AL") _
                  + Application.CountIf(vRng2, "VL")
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With

 End Sub
Click to expand...

EXCEL MAX said:
If you want to loop through all columns from C to AL and
count the number of cells that contain "AL" or "VL" or they are blank you can try this...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range, vRng2 As Range
    Dim vLastRow As Long
    Dim vRngCol As Range

    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            Set vRng2 = Range(Cells(3, vRngCol.Column), _
                              Cells(vLastRow, vRngCol.Column))
            LvTtl = Application.CountBlank(vRng2) _
                  + Application.CountIf(vRng2, "AL") _
                  + Application.CountIf(vRng2, "VL")
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With

 End Sub
Click to expand...
This works well. Thx (y):coffee:?
 
Upvote 0
EXCEL MAX said:
If you want to loop through all columns from C to AL and
count the number of cells that contain "AL" or "VL" or they are blank you can try this...
VBA Code: 
Sub GetAbsence15()

    Dim vRng As Range, vRng2 As Range
    Dim vLastRow As Long
    Dim vRngCol As Range

    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            Set vRng2 = Range(Cells(3, vRngCol.Column), _
                              Cells(vLastRow, vRngCol.Column))
            LvTtl = Application.CountBlank(vRng2) _
                  + Application.CountIf(vRng2, "AL") _
                  + Application.CountIf(vRng2, "VL")
            If IsWeekend(vRngCol.Cells(1)) = False Then
                If LvTtl >= 2 Then
                    vRngCol.Font.Size = 18
                Else
                    vRngCol.Font.Size = 12
                End If
            End If
        Next vRngCol
    End With

 End Sub
Click to expand...
Hi, Is it possible to change the code to run as array in order to speed it up cos I have others codes to run together.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Highlight Columns if the date falls to weekend or holidays
2
Replies
12
Views
2K
johnnyL
J
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
I
  • Question Question
Advice to move/edit array from module to worksheet
Replies
6
Views
224
ipbr21054
I
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
311
Logit
Logit

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top