#Value when applying filter using VBA

  • Hi


    So, in order to calculate the amount of cells not in bold in a column (that are set manually), I've created the formula "SENEGRITO" (IFBOLD in portuguese) using the module:


    Code
    Public Function SENEGRITO(MyCell As Range) As Variant
    SENEGRITO = MyCell.Font.Bold
    End Function


    I then added a new column with the formula:

    Code
    =SENEGRITO([@Início])

    and so I could count the amount of "FALSE" there.


    And it worked great. The thing is, whenever I filter the table using

    Code
    Private Sub Filtrar_int_Click()
      ActiveSheet.ListObjects("Intimacoes").Range.AutoFilter Field:=6, Criteria1:=""
    End Sub


    all the results from the formula SENEGRITO return #Value.


    If I then run

    Code
    Private Sub Mostrar_int_Click()
      ActiveSheet.ListObjects("Intimacoes").AutoFilter.ShowAllData
    End Sub


    It all works again.


    If instead of filtering using the VBA code, I apply the same filter manually, there's no error.
    If I edit a cell with the error mesagem without changing anything in it, this cell (and only it) shows the correct value.


    Does anyone now why this is happening and what I could do to fix it?


    I know that changing the format from a cell to bold is not ideal as a way of marking it, but the table is already complex, and is also used by people with no experience in excel, so I'm trying to make it as simple as possible for the end user.

  • Thank you for the reply, but I'm not sure on how to make it work.


    Does your sugestion mean that I should replace the code in the module with

    Code
    Public Function SENEGRITO(MyCell As Range) As Variant
    Set rngData = MyCell
      For Each TBItem In rngData
     
    SENEGRITO = TBItem.Font.Bold
    
    
    Next
    End Function

    ?


    Because I got the same results doing so.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!