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

archleo · Oct 7th 2013

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

Hi Holycow,

This is a very clean and optimized code compared with mine. Thnx for your efforts pal. Especially I did like this part

Code

If InStr(LCase(ThisWorkbook.FullName), "backups") = 0 Then 'then it's not a backup so update DMS logs, no need for helper in AA1

As far as I understand from this code is, you simply search for "backups" string in the whole filename w/ its path to find out provided the opened file resides in the BACKUPS folder or not. Very clever method pal, really so smart!! You are saving my time and I really learn a lot from these snippets. Excel and VBA are both deep worlds and I like coding Excel for my needs.

holycow · Oct 7th 2013

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

Impressed, you figured out InStr all on your own

There is more professional coding to update the DMS logs using GetObject, but I'm not full bottle on that yet.

BTW that userform where you tick boxes for the filtering criteria is a great idea.

snb · Oct 7th 2013

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

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .ScreenUpdating = False 
        .EnableEvents = False 
        .DisplayAlerts = False 
     
    If InStr(LCase(ThisWorkbook.FullName), "backups") = 0 Then 'then it's not a backup so update DMS logs, no need for helper in AA1
        with getobject("C:\Users\" & Environ("UserName") & "\Dropbox\ESSO\00. TURNER SUBMITTALS\2D_EMAAR_DMS.xlsm") 
          .Worksheets("FLAGS").range("B2:B5")=application.transpose(array("0","","date,time)) 
          .Close True 
        End With 
         
        BackupLog 
    Else 
        ThisWorkbook.Saved = True 'if it's a backup copy, then don't even ask to Save, just Exit
    End If 


        .DisplayAlerts = True 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
End Sub

Display More

Why 'killing' what will be replaced anyway ?

Code

Sub BackupLog() 
    application.displayalerts=false
    ThisWorkbook.Save 
     
    c00 = "C:\Users\" & Environ("UserName") & "\Dropbox\ESSO\08. BACKUPS\2D_RFI Response_Log_" & Format(Date, "YYYYMMDD") & ".xlsm"      


    On Error Resume Next 'turn off error checking, also you can delete udf that checks if file exists
    SetAttr c00, vbNormal
    ThisWorkbook.SaveAs c00 
    SetAttr c00, vbReadOnly 'set the backup copy to read-only
End Sub

Display More

archleo · Oct 7th 2013

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

Dear Holycow,
Dear snb

Please do find my new files optimized as per your code snippets.

https://www.dropbox.com/s/4zra5lgr9kceaml/MyExcelFiles-New.rar

archleo · Oct 7th 2013

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

BTW Holycow,

I finally solved the problem for the cell formula errors during copy-paste...I also have it cut&paste to the respective chart, by this way you always have the respective data table according your filtering criteria and chart is also updated thus.
I have integrated this code with a piece of snippet which exports the selected sheets as PDF and thus added an option to select if you want a chart with your report or not. As usual the codes are a little bit long

archleo · Oct 7th 2013

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

Quote from snb;686922

Code

.Worksheets("FLAGS").range("B2:B5")=application.transpose(array("0","","date,time))

Hi snb,

The above line of your snippets creates an error:

Compile error:
Expected: list seperator or )

FYI...

snb · Oct 7th 2013

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

You can easily restore that...

archleo · Oct 7th 2013

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

Quote from snb;686931

You can easily restore that...

Sorry for my idiotic message...I later on see the excessive " sign in front of date...

archleo · Oct 7th 2013

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

Hi snb,

I have replaced my code with yours, however an error occurs "Workbook _Global object variable has failed" and when it is debugged it highlights the UserForm1.Show code of 2D_EMAAR_DMS.xlsm file. I couldn't be able to manage it out, therefore I've switched back to the codes of Holycow which works fine and wo/ any problems.

holycow · Oct 8th 2013

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

Hi archleo and snb

I have had a look into the problem of Post #49. By making the following change to the 2D_EMAAR_DMS.xlsm file snb's code works perfectly.

Code

If Windows(ThisWorkbook.Name).Visible = True Then
        UserForm1.Show False 'enclosed this line in an if statement
    End If

I'm not sure if this is a dodgy workaround. Please let us know snb.

snb · Oct 8th 2013

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

holycow,

I don't think it's a dodgy workaaround.

I had no look into the file, I only read the code and rewrote some of it.
So I had no idea the workbook could have been hidden.

holycow · Oct 8th 2013

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

Thank you snb

Just to clarify the DMS workbook is not actually hidden. When this code "opens" it, it "opens" it hidden.

Code

With getobject("C:\Users\" & Environ("UserName") & "\Dropbox\ESSO\00. TURNER SUBMITTALS\2D_EMAAR_DMS.xlsm") 
                .Worksheets("FLAGS").range("B2:B5")=application.transpose(array("0","","date,time)) 
                .Close True 
            End With

The Workbook_open code contained in it, then tries to load UserForm1, causing it to bug out.

Edit: Weird though because you did set enable events to false

archleo · Oct 21st 2013

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

Hi holycow,
Hi snb,

I would like to construct something nice with VBA, I've searched the forum for similar idea, but the one that I've been able to find did not solved my idea yet. So I would like to discuss it with you:

if you look at my RFI log workbook and RFI_LOG sheet; I use P, Q and R columns values for sensitivity charting data, O column values for horizontal axis values and C column is for the arrival date of the RFI document.
I thought; it would be a nice idea that everytime I enter a data to this log:

1) With respect to the date's month value in C column (lets say it is XX) and P, Q and R cells are not empty
1.1) if Sensitivity-XX13 chart sheet exists, than update the series and x-axis values with this new data added
1.2) if Sensitivity-XX13 chart sheet does not exists (possibly because a new month has begun so it will be the first data of that month), create the chart sheet (may be by copying the the previous month's chart sheet, renaming it and then updating the series data)

What are your opinions about this? Am I again searching for the hard way or extending a possible simpler route too much? Guys, please do consider that I'm an engineer :lol:

EDIT: I've found this page with Uncle Google but I'm a little bit confused about it on how to apply this to my situation.

http://peltiertech.com/WordPress/dynamic-chart-source-data/

holycow · Oct 21st 2013

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

Hi archleo
Nice to see you again :). I think it would be good to start a new thread for your new idea.
More people will look at a new thread, than an old one with a lot of replies. Also because it really is a new query and not related to this thread title.
It's not that I don't want to help you and I will try, but I'm not that great with charts.

archleo · Oct 21st 2013

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

Hi holycow,

I thought to start a new thread but as it is related to my - so called - Excel DMS Project, I just wrote it up here to hear your advises and thoughts. I'm just cutting this off now and giving a fresh new start with a thread. Thnx for you efforts pal. I'll post the link of that thread here also. Hoping to hear from you soon...

archleo · Oct 21st 2013

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

Hi holycow,
Hi snb,

I have created a new thread thanks to the kind opposition of holycow. Here is the forum link for your ease:

http://www.ozgrid.com/forum/showthread.php?t=183549&p=688536#post688536

snb · Oct 22nd 2013

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

Code

Private Sub UserForm_Initialize()
    cbMonth.list=application.getcustomlistcontent(4)
    cbMonth.ListIndex = Sheets("DATA").Range("Z2").Value - 1
    
    cbYear.List=[transpose(row(2013:2020))]
    cbYear.ListIndex = Sheets("DATA").Range("Z3").Value -2013
End Sub

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

Participate now!

Share