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

holycow · Sep 12th 2013

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

Good news and bad news.

Bad news - As I said I only tested code in Post #20 once. I just got a free minute so I tested it again and it failed.

Good news - I tried another thing as well and it worked, but I will test umpteen more times before I post.

holycow · Sep 12th 2013

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

I think I have the solution.

Firstly you need to use the amended UDF as per Post #20.

Secondly put Calculate at the end of the main code as per code snippet below

Code

'CLEAR THE FILTERING
    Sheets("RFI_LOG").Select
    ActiveSheet.Range("$A$1:$I$270").AutoFilter Field:=3
    Sheets("DATA").Select
    Calculate  'ADDED THIS LINE HERE
    Application.ScreenUpdating = True


End Sub

And thirdly make sure to delete the workaround code.

archleo · Sep 13th 2013

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

Hi holycow,

I've changed the code as per your proposal and the macro worked like a charm. It does not give that #VALUE error. BUT :spin:
Yes, unfortunately there is a BUT. The problem is with the code snippet which I copy the table and paste it as a picture on the DATA sheet.

Code

'******************************************************************
        '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

Display More

There is no problem with the filtering cause I've controlled it with temporarily disabling the code which reverts the filtering back after I copy the table in DATA sheet. As all the formulas and the UDF is currently in working mode, the picture of the data table shall contain no problems. But though the table doesn't contain any #VALUE errors after filtering, the picture of the table shows #VALUE errors at those cells.

Here is the picture after the filtering:
[Blocked Image: http://img43.imageshack.us/img43/1905/mg0w.jpg]

And this is the screen cap from the RFI_LOG sheet, which is filtered and where the values are linkted to the table in DATA sheet:
[Blocked Image: http://img703.imageshack.us/img703/2963/bces.jpg]

As you will notice, the main values in the filtered sheet does not contain any errors, and the values of the cells reflects exactly the same in the table of DATA sheet.
I couldn't understand why this God damn table picture reflects #VALUE errors??

holycow · Sep 13th 2013

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

Code

'******************************************************************
 '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

Display More

What is reason for the table being a bitmap as per commented ??? above?

archleo · Sep 13th 2013

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

Hi pal,

The reason is simply I cut&paste this bitmap over my monthly charts that I derive from the data in RFI_LOG sheet. I haven't tried any another method than this and provided there is, I really dunno know. If you look at the chart sheets you will notice these bitmaps on the right top corner of the chart.

Regards,

holycow · Sep 13th 2013

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

Fingers crossed

Go back to your original UDF, ie take Application.Volatile out.

Go back to your original button code as per below. I have just made a few minor changes.

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
    Application.Calculation = xlCalculationManual
    '******************************************************************
    '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
    Calculate
    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
    Sheets("DATA").Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

Display More

archleo · Sep 13th 2013

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

Here are the results of the Excelian Jury

Run-time error '1004'
Microsoft Excel cannot paste data
:rambo::rambo:

holycow · Sep 14th 2013

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

Upload latest workbook containing everything as per Post #26

archleo · Oct 1st 2013

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

Quote from holycow;683919

Upload latest workbook containing everything as per Post #26

Hi holycow,

I've been under a surgery so I were away from everything for about 10 days. I'm home now for a recovery period, and just writing this lines to you so that you won't have bad things about me in your head. I will look where we have been stopped last time, and keep you informed in a couple of days. Thnx for your understanding and outstanding help.

Regards,

holycow · Oct 1st 2013

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

No problem. Hope you have a speedy recovery.

archleo · Oct 4th 2013

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

Hi Holycow,

I have looked thru our postings and worked a little bit on the problem, couldn't find a solution however found a workaround. After a successfull filtering via VBA and using Calculate command to eliminate the errors, I copy the table, paste it with column with options and then paste it again with preserving values and number formatting and then pasting it as a bitmap :))) A little bit struggling thing but it works. Please do find the workbook attached with my latest revisions on the coding. As you are an experienced programmer, you can find the code too long and may be unnecessary, therefore I highly appreciate provided you could support me to shorten it or optimize it (if it's possible for sure).

Also I have created a seperate file (2D_EMAAR_DMS.xlsx) which we use to control opening and closing status of our log files, because our files resides in a shared folder in Dropbox. You'll see when you examine the coding.

Regards and many thanks for your help and support. I'm on my ears to hear your comments and propositions.

NOTE: I couldn't be able to attach my files to the forum, so you can download them from the below link

https://www.dropbox.com/s/fmhr2xz1e17ko6d/MyExcelFiles.rar

snb · Oct 4th 2013

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

Holy smoke,

This

Code

Sub tst()
    m = cbMonth.ListIndex + 1
    If m = 13 Then
        moT = "CUMULATIVE"
    Else
        moT = UCase(Application.Text(DateSerial(2013, m, 1), "mmmm"))
        dm = Day(DateSerial(cbyear, m + 1, 0))
    End If
End Sub

can be a replacement for

Code

sub tst()
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

Display More

holycow · Oct 4th 2013

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

Hi snb

The initial problem in this thread was to sort out cell errors on filter so I was focused only on that. I did not worry about shortening existing code. Why look for more work?? Now the user has asked for shortening/optimising of code your suggestion has come at a good time. Thanks for the help it's always good to learn from you.

holycow · Oct 4th 2013

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

Hi archleo

Just looking at the procedure BackupLog. There is quite a bit of shortening that can be done there but need to ask a question first.

You have used this save code. Using this code the activeworkbook name changes upon save.

Code

ThisWorkbook.SaveAs defDir & fName

Are you aware of this save code? By using this save code the activeworkbook does not change. You continue to have the same workbook open while it saves a copy to your specified location but carries on working in the original file. This is the method I would use for backups.

Code

ThisWorkbook.SaveCopyAs defDir & fName

Try it in your workbook and see what you think.

snb's code for the date values is a no brainer and you should change to that.

Let's know how those changes go before we continue.

archleo · Oct 4th 2013

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

Hi snb,

Thanks for the code. I'm not so new but also not so experienced in VBA, but I do understand the logic of it. I really learned a lot from this code. I do appreciate. Thnx my friend.

archleo · Oct 4th 2013

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

Hi Holycow,

Yes I'm aware of the save code. If you have noticed, prior to this code I save my original workbook and after that I make a "saveas" copy. And also this backup code is triggered via Workbook.Close event. To eliminate any loop, I'm using a control cell with .Value = 1 and the value of this cell in the copied file ise 0, so that when you need to or accidentally open a backed up file, you don't mess anything.

I'm now on my way changing my coding with snb's...

Thnx for all your efforts...

holycow · Oct 4th 2013

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

Quote

I'm now on my way changing my coding with snb's...

snb is the master of short code, let's hope he sticks around

Quote

I'm using a control cell with .Value = 1 and the value of this cell in the copied file ise 0, so that when you need to or accidentally open a backed up file, you don't mess anything

Ok now I understand what the 0 and 1 do. Good idea of not messing up the backup file.

archleo · Oct 5th 2013

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

Quote from holycow;686707

snb is the master of short code, let's hope he sticks around

Ok now I understand what the 0 and 1 do. Good idea of not messing up the backup file.

Thnx for your appreciation bro. Most of the time, before writing any code, I prepare a story-board like text to realize what I want to do and opt out my options and criteria. Then I divide it into pieces and search for help files, uncle google and vba forums for a "dummy's guide on how to..." I also frequently use Mr.Excel's record macro option to see how it works. No need to open a new era, huh? As I'm not an IT guy or a programmer, and because of my working style described above, my code lines could be a little bit long, harsh and smokey Thanks for the novice guys like you, snb and others, I'm step by step learning the logic of constructing clean, optimised and understandable codes. Once again thanks a lot for your hands on improving and expanding my knowledge.

holycow · Oct 5th 2013

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

snb

Quote

snb's code for the date values is a no brainer and you should change to that

Just in case that get's misinterpreted, I meant the question of which is the better code is a no brainer.

archleo

* Post deleted, need to rethink *

holycow · Oct 5th 2013

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

Quote

... As I'm not an IT guy or a programmer ...

If that's the case you have done an excellent job on your workbook then.

Here is a modified code for Workbook_BeforeClose

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With


    If InStr(LCase(ThisWorkbook.FullName), "backups") = 0 Then 'then it's not a backup so update DMS logs, no need for helper in AA1
    
        DMS = "C:\Users\" & Environ("UserName") & "\Dropbox\ESSO\00. TURNER SUBMITTALS\2D_EMAAR_DMS.xlsm"
        
        Workbooks.Open DMS
        With ActiveWorkbook.Worksheets("FLAGS")
            .Range("B2").Value = "0"
            .Range("B3").Value = ""
            .Range("B4").Value = Date
            .Range("B5").Value = Time
            .Parent.Close True
        End With
        
        Call Module1.BackupLog 'Backup log
    Else
        ThisWorkbook.Saved = True 'if it's a backup copy, then don't even ask to Save, just Exit
    End If
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Display More

And here is a modified code for BackupLog

Code

Sub BackupLog()
    
    Dim fName As String
    Dim defDir As String
    
    ThisWorkbook.Save
    
    defDir = "C:\Users\" & Environ("UserName") & "\Dropbox\ESSO\08. BACKUPS\"
    fName = "2D_RFI Response_Log_" & Format(Date, "YYYYMMDD") & ".xlsm" 'no need for helper cells in AB1:AB3
    
    On Error Resume Next 'turn off error checking, also you can delete udf that checks if file exists
    SetAttr defDir & fName, vbNormal 'prepare to kill it anyway
    Kill defDir & fName 'kill it anyway
    On Error GoTo 0 'turn back on error checking
    
    ThisWorkbook.SaveAs defDir & fName
    SetAttr defDir & fName, vbReadOnly 'set the backup copy to read-only
    
    MsgBox "2D RFI LOG HAS BEEN BACKED UP TO YOUR BACK-UP FOLDER" & vbCrLf & "AS OF DATE " & Date & " @ " & Time & vbCrLf & vbCrLf & "DEFAULT BACKUP FOLDER:" & vbCrLf & defDir
        
End Sub

Display More

Quote

... Thanks for the novice, guys like you ...

I'm not in the same league as the big guys on this site, I'm still on my P Plates, maybe even L Plates.

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

Participate now!

Share