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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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: 7
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
T
  • Question
I have a code that I don't know how to use
Replies
6
Views
333
MARK858
MARK858
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
246
birdman15
B
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
378
sspatriots
S
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
296
Gajendran Yadhav
G

Forum statistics

Threads
1,215,077
Messages
6,122,995
Members
449,094
Latest member
masterms

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