If / Then / Else Statement Difficulties

[Swoootie]

Hi all,

I am currently working on a project to automate a report that is run monthly. In order to do this I have made it dynamic so that it can reference files based on the run date etc and it is working quite well.

What has got me a bit stumped is what I think should be a straightforward thing to do; however, every time I attempt to run my code it doesn't present the expected results.

Basically the main spreadsheet is a template that is overwritten monthly by data entered through a variety of sources. In a particular cell reference on one of my spreadsheet tabs I have entered a formula to compare the current results with results from 3 months ago. I have set a restriction (which works fine) where if there isn't a file relating to a month from 3 months ago it populates a "N/A" in the required cell, else it populates the date from 3 months ago.

What I am having trouble with is where there is a date entered in the cell (ie where there is a file) I want to open the related file and copy across the results from that file into my template spreadsheet. Where there is no file available I would like the cell to be highlighted in a green colour.


An extract from my code (which does not work) is:

Dim WKBOOK As Workbook
Dim FILEPATH As String
Dim FILENAME As String
Dim PREVMTH As String

FILEPATH = "C:\"
FILENAME = "Monitoring Report" & Format(PREVMTH, "MMM YYYY") & ".xls"


Windows( _
"Monitoring Report - TEMPLATE_1.xls"). _
Activate
PREVMTH = Sheets("Monitoring Front Page").Range("B9").VALUE
Sheets("Monitoring Front Page").Select
Range("C9").Select
If (IsDate(PREVMTH) = True) Then Workbooks.Open (FILEPATH & FILENAME)
Windows("Monitoring Report - TEMPLATE_1.xls"). _
Activate
Sheets("Monitoring Front Page").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = WKBOOK.Sheets("Monitoring Front Page").Range("C5").VALUE
Else: Range("C3") = "GREEN"
End If


Any help or guidance would be greatly appreciated.

Please let me know if you require any further explanations or details.

Thanks

 

[Andrew Poulsom]

Is Monitoring Report - TEMPLATE_1.xls the name of the workbook that contains your VBA code?

 

[Swoootie]

Hi Andrew,

Yes it is.

 

[Andrew Poulsom]

Try (untested):

Sub Test()
    Dim PREVMTH As String
    Dim FILEPATH As String
    Dim FILENAME As String
    Dim WKBOOK As Workbook
    PREVMTH = ThisWorkbook.Sheets("Monitoring Front Page").Range("B9").Value
    FILEPATH = "C:\"
    FILENAME = "Monitoring Report" & Format(PREVMTH, "MMM YYYY") & ".xls"
    If IsDate(PREVMTH) Then
        Set WKBOOK = Workbooks.Open(FILEPATH & FILENAME)
        ThisWorkbook.Sheets("Monitoring Front Page").Range("C9").FormulaR1C1 = WKBOOK.Sheets("Monitoring Front Page").Range("C5").Value
    Else
        ThisWorkbook.Sheets("Monitoring Front Page").Range("C3").Interior.ColorIndex = 10
    End If
End Sub

 

[Swoootie]

That seems to work brilliantly.

Thank you so much for your help.