Filtering macro fills a column of my sheet with #value error

archleo · Sep 9th 2013

Hi pals,

I've stucked in with one of my macros which drives me crazy. Here what it is and any help is highly appreciated.

I have an excel file which I've constructed to follow contractor issued RFI documents in our construction. My sheet is a few simple columns i.e. RFI#, RFI Description, Issue Date, Must Reply Date (which is Issue Date + 7d), Return Date and Closed Status. When an RFI document arrives, the value of the cells in Return Date column are always =TODAY() till it is actually replied. The value of the cells in the Closed Status column are always an UDF (IsFormula) checking the cell value of Return Date column and if it is a formula the cell value becomes simply "NO", and "YES" otherwise. The sheet is working well and has no problems.

However, every month I have to prepare a Monthly Progress Report and in this report I have to place a sensitivity analysis for the regarding month. Meaning; I'm analyzing the reply times of RFI documentation from the date it is issued. Finaly I prepare a bar graphic for this data. At this graph, I'm placing a picture of this sensitivity and efficieny analysis. For this picture; I'm copying the related cells and paste it as a picture to my graph. As the data is huge, I've place an UserForm to select date and year data for analysis and pass the values of this combos as a variable to a macro which filters the main table according to these.

The problem is: Macro works well wo/any errors. However, the macro replaces the values of the cells in Closed Status column (which I have an UDF) with #VALUE error. Therefore, the data table that I calculate the sensitivity and efficiency values gives errors. But when I filter my data sheet with the Excel's built-in filter, this problem interestingly do not occur.

Code

Application.ScreenUpdating = False
    
        '******************************************************************
        'FILTER THE VALUES
        '******************************************************************
        If cbMonth.ListIndex < 12 Then
            Sheets("RFI_LOG").Select
            ActiveSheet.Range("$A$1:$I$15700").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(1, SortString)
        ElseIf cbMonth.ListIndex = 12 Then
            Sheets("RFI_LOG").Select
            ActiveSheet.Range("$A$1:$I$15700").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(0, SortString)
        End If
        '******************************************************************
        'CREATE TABLE IMAGES
        '******************************************************************
        Sheets("DATA").Select
        If cbMonth.ListIndex < 12 Then
            ActiveSheet.Range("A5").Value = moT & " " & YearString & " SENSITIVITY ANALYSIS"
            ActiveSheet.Range("G5").Value = moT & " " & YearString & " STATUS LOG"
        ElseIf cbMonth.ListIndex = 12 Then
            ActiveSheet.Range("A5").Value = "YEAR " & YearString & " " & moT & " SENSITIVITY ANALYSIS"
            ActiveSheet.Range("G5").Value = "YEAR " & YearString & " " & moT & " STATUS LOG"
        End If
        ActiveSheet.Range("A5:I11").Select
        Selection.Copy
        Range("A13").Select
        ActiveSheet.Pictures.Paste.Select
        Application.CutCopyMode = False
        'CLEAR TABLE HEADERS
        ActiveSheet.Range("A5").Value = ""
        ActiveSheet.Range("G5").Value = ""
        'CLEAR THE FILTERING
        Sheets("RFI_LOG").Select
        ActiveSheet.Range("$A$1:$I$15700").AutoFilter Field:=3
        Sheets("DATA").Select
    
    Application.ScreenUpdating = True

Display More

Any help is highly appreciated. Thank you for your efforts. Provided it's needed, I may attach my Excel file for reviewing.

COMPUTER
OS: Windows 7 Professional
Version: 6.1.7601 SP1
System Type: 64bit

OFFICE SET
Product: Microsoft Excel 2010 Standard Ed.
Version: 14.0.6129.5000 SP1
Office Type: 32bit

holycow · Sep 9th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from archleo;683111

... Provided it's needed, I may attach my Excel file for reviewing ...

Yep, it's needed.

archleo · Sep 9th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from holycow;683131

Yep, it's needed.

I've edited my post and attached the file as needed. Thanks for your help pal.

holycow · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from archleo;683111

... I've stucked in with one of my macros which drives me crazy ...

I have been trying to figure this out and it has driven me crazy too.

Unless someone else can come up with a solution, I am going to have to suggest that you amend the code to copy and paste to values in Column I before the filter code runs and then some code to put formulas back in again when done.

Appreciate if someone could take a look why IsFormula UDF does not work on filtered data.

Edit: IsFormula UDF works on manually filtered data, but not when filtered by code.

archleo · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from holycow;683288

I have been trying to figure this out and it has driven me crazy too.

Unless someone else can come up with a solution, I am going to have to suggest that you amend the code to copy and paste to values in Column I before the filter code runs and then some code to put formulas back in again when done.

Appreciate if someone could take a look why IsFormula UDF does not work on filtered data.

Edit: IsFormula UDF works on manually filtered data, but not when filtered by code.

Display More

Thnx for your efforts pal. The UDF is simply:

Code

=IF(IsFormula(E2)=TRUE;"No";"Yes")

During filtering I think Mr.Excel hides the rows which contains irrelevant data according to the filtering criteria. And when I look at the usage of the UDF, the addressing of the cell is simply just "E2". Do you think changing this simple reference to "$E2" or "$E$2" might be a workaround?

Regards,

EDIT: I have tried it but it didn't worked out anyway :rambo:

holycow · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

You bet me to it, I was just about to post that.

You can change your formula to this =If(IsFormula(E2);"No";"Yes"). As it is now it is saying True=True. Not that that will fix the problem though It will just tidy up the formula.

archleo · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

Thnx for the nice tip pal. I haven't noticed that though. And for sure it didn't changed anything @ the end

holycow · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

For now until you get a better solution, place this code immediately before filter on

Code

With Range("I2:I" & Range("B2").End(xlDown).Row)
        .Value = .Value
    End With

And this immediately after filter off

Code

With Range("I2:I" & Range("B2").End(xlDown).Row)
            .Value = "=IF(IsFormula(E2),""No"",""Yes"")"
        End With

archleo · Sep 10th 2013

Re: Filtering macro fills a column of my sheet with #value error

Hi holycow,

I've tried your solution but at the time of running, the VB gives the following error

Quote

Run-time error '1004':
Application-defined or object defined error

When I hit debug, the debugger highlights the code:

Code

.Value = "IF(IsFormula(E2);""No"";""Yes"")"

I placed the code bunch that you've given like below

Code

Application.ScreenUpdating = False
    
        '******************************************************************
        'FILTER THE VALUES
        '******************************************************************
        If cbMonth.ListIndex < 12 Then
            Sheets("RFI_LOG").Select
            With Range("I2:I" & Range("B2").End(xlDown).Row)
                .Value = .Value
            End With
            ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(1, SortString)
            With Range("I2:I" & Range("B2").End(xlDown).Row)
                .Value = "=EGER(IsFormula(E2);""No"";""Yes"")"
            End With
        ElseIf cbMonth.ListIndex = 12 Then
            Sheets("RFI_LOG").Select
            With Range("I2:I" & Range("B2").End(xlDown).Row)
                .Value = .Value
            End With
            ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(0, SortString)
            With Range("I2:I" & Range("B2").End(xlDown).Row)
                .Value = "=EGER(IsFormula(E2);""No"";""Yes"")"
            End With
        End If
.
.
.
.


        'CLEAR THE FILTERING
        Sheets("RFI_LOG").Select
        ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3
        With Range("I2:I" & Range("B2").End(xlDown).Row)
            .Value = "=EGER(IsFormula(E2);""No"";""Yes"")"
        End With
        Sheets("DATA").Select
    
    Application.ScreenUpdating = True

Display More

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

This is the whole code for command button.

Code

Private Sub CommandButton1_Click()
    ufSorting.Hide
    Dim dm As Integer
    Dim m As Integer
    Dim SortString As String
    Dim YearString As String
    Dim moT As String
    
    YearString = cbYear.Text
    
    If cbMonth.ListIndex = 0 Then 'Ocak
        dm = 31
        m = 1
        moT = "JANUARY"
    ElseIf cbMonth.ListIndex = 1 Then 'Subat
        If cbYear.Value Mod 4 = 0 Then
            dm = 29
        Else
            dm = 28
        End If
        m = 2
        moT = "FEBRUARY"
    ElseIf cbMonth.ListIndex = 2 Then 'Mart
        dm = 31
        m = 3
        moT = "MARCH"
    ElseIf cbMonth.ListIndex = 3 Then 'Nisan
        dm = 30
        m = 4
        moT = "APRIL"
    ElseIf cbMonth.ListIndex = 4 Then 'Mayis
        dm = 31
        m = 5
        moT = "MAY"
    ElseIf cbMonth.ListIndex = 5 Then 'Haziran
        dm = 30
        m = 6
        moT = "JUNE"
    ElseIf cbMonth.ListIndex = 6 Then 'Temmuz
        dm = 31
        m = 7
        moT = "JULY"
    ElseIf cbMonth.ListIndex = 7 Then 'Agustos
        dm = 31
        m = 8
        moT = "AUGUST"
    ElseIf cbMonth.ListIndex = 8 Then 'Eylül
        dm = 30
        m = 9
        moT = "SEPTEMBER"
    ElseIf cbMonth.ListIndex = 9 Then 'Ekim
        dm = 31
        m = 10
        moT = "OCTOBER"
    ElseIf cbMonth.ListIndex = 10 Then 'Kasim
        dm = 30
        m = 11
        moT = "NOVEMBER"
    ElseIf cbMonth.ListIndex = 11 Then 'Aralik
        dm = 31
        m = 12
        moT = "DECEMBER"
    ElseIf cbMonth.ListIndex = 12 Then 'Tüm Kayit
        dm = 31
        m = 12
        moT = "CUMULATIVE"
    End If
        
    SortString = m & "/" & dm & "/" & YearString
    
    Application.ScreenUpdating = False
    
    With Range("I2:I" & Range("B2").End(xlDown).Row) 'AMENDED YOUR CODE WITH THIS
        .Value = .Value
    End With
    
        '******************************************************************
        'FILTER THE VALUES
        '******************************************************************
        If cbMonth.ListIndex < 12 Then
            Sheets("RFI_LOG").Select
            ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(1, SortString)
        ElseIf cbMonth.ListIndex = 12 Then
            Sheets("RFI_LOG").Select
            ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3, Operator:= _
                xlFilterValues, Criteria2:=Array(0, SortString)
        End If
        '******************************************************************
        'CREATE TABLE IMAGES
        '******************************************************************
        Sheets("DATA").Select
        If cbMonth.ListIndex < 12 Then
            ActiveSheet.Range("A5").Value = moT & " " & YearString & " SENSITIVITY ANALYSIS"
            ActiveSheet.Range("G5").Value = moT & " " & YearString & " STATUS LOG"
        ElseIf cbMonth.ListIndex = 12 Then
            ActiveSheet.Range("A5").Value = "YEAR " & YearString & " " & moT & " SENSITIVITY ANALYSIS"
            ActiveSheet.Range("G5").Value = "YEAR " & YearString & " " & moT & " STATUS LOG"
        End If
        ActiveSheet.Range("A5:I11").Select
        Selection.Copy
        Range("A13").Select
        ActiveSheet.Pictures.Paste.Select
        Application.CutCopyMode = False
        'CLEAR TABLE HEADERS
        ActiveSheet.Range("A5").Value = ""
        ActiveSheet.Range("G5").Value = ""
        'CLEAR THE FILTERING
        Sheets("RFI_LOG").Select
        ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3
        
        With Range("I2:I" & Range("B2").End(xlDown).Row) ' AMENDED YOUR CODE WITH THIS
            .Value = "=EGER(IsFormula(E2);""No"";""Yes"")"
        End With
        Sheets("DATA").Select
    
    Application.ScreenUpdating = True
End Sub

Display More

archleo · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from holycow;683386

This is the whole code for command button.

Code

Private Sub CommandButton1_Click()
.
.
.        
        With Range("I2:I" & Range("B2").End(xlDown).Row) ' AMENDED YOUR CODE WITH THIS
            .Value = "=EGER(IsFormula(E2);""No"";""Yes"")"
        End With
        Sheets("DATA").Select
    
    Application.ScreenUpdating = True
End Sub

Display More

Holycow, did you run this code /wo any problems? I've tried it, the VB gives the same error ('1004') and debugger highlights the .Value code block. When I look at the sheet, the cells are filled with the formula however the cell shows #VALUE error I think I will shoot Mr.Excel with a 7.65 calibre at the end

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Yes tested in English before and tested again just now and it works.

"=IF(IsFormula(E2),""No"",""Yes"")"

Assume this is the correct equivalent in Turkish. Please confirm.

"=EGER(IsFormula(E2);""No"";""Yes"")"

archleo · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from holycow;683401

Yes tested in English before and tested again just now and it works.

"=IF(IsFormula(E2),""No"",""Yes"")"

Assume this is the correct equivalent in Turkish. Please confirm.

"=EGER(IsFormula(E2);""No"";""Yes"")"

Display More

In fact the word "IF" in English is equivalent to "EĞER" in Turkish. However, I cannot type the letter "Ğ" in VBA module, where I think the problem might be, however I've discovered that it does not matter even if you write "=EGER..." or "=EĞER..." in the formula bar in Excel, Mr. Excel automatically converts it. Weird isn't it?

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Can you post exactly what appears in the formula bar when you type the formula manually.

archleo · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Holycow,

Please do find attached "screen.jpg".

Also when I run the macro, though it gives the 1004 error it seems that it does its job and places the formula. But I could'nt solve why it shows #VALUE error. When you hit the cell E2 to edit and press Enter /wo changing anything, the error values automatically changes and reflects the true values. Weird...weird...weird...You can see the screen cap from the below link

[Blocked Image: http://img844.imageshack.us/img844/5528/3vf5.jpg]

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

I need to see what's in formula bar when formula does not error. Please show formula before macro runs.

archleo · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

Quote from holycow;683428

I need to see what's in formula bar when formula does not error. Please show formula before macro runs.

Screen.jpg file attached to my previous post is what you are asking for my friend. It shows the formula bar before the macro runs.

archleo · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

While searching the net for my issue, I've found an interesting thread in social.msdn.microsoft.com forum...
As far as I've understood, the problem is with the Locale and Region settings...
But interestingly my settings are true just that's been said in the threads here:

http://social.msdn.microsoft.com/Forums/office/en-US/ac672d85-3a88-488d-9687-4d0b71a9b357/cellfilename-function-gives-value-errors-with-turkish-language

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

The copy and paste values was only a workaround anyway as you know. I'll try another workaround.

holycow · Sep 11th 2013

Re: Filtering macro fills a column of my sheet with #value error

I may have found solution and it's not a workaround.

Try changing UDF to this

Code

Function IsFormula(ByVal Ref As Range) As Boolean
    Application.Volatile 'ADDED THIS LINE
    If Ref.HasFormula = False Then
        IsFormula = False
    Else
        IsFormula = True
    End If
End Function

I only tested once and it worked, now it's getting late. I will check back tomorrow.

Filtering macro fills a column of my sheet with #value error

Participate now!

Share