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.
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