If / Then / Else Statement Difficulties

Swoootie

Board Regular
Joined
Jun 28, 2012
Messages
66
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is Monitoring Report - TEMPLATE_1.xls the name of the workbook that contains your VBA code?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try (untested):

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,643
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top