If function (Excel VBA)

[eddiesvoicebox]

Afternoon all,
I am looking to add an if function (i think) to my report runner in excel. currently it opens an Access database and runs some code but on certain dates (weekends and bank holidays) i want to run a different bit of code in access.
My question is this a) Can i use a list of dates in VBA i.e
if Date = "01/01/05","02/01/05 then 'run other code' else 'normal process'

b) if so how do i separate the dates (i have about 107 of them)

c) or am i way off the mark and can someone please advise on this.


Many thanks in advance for any time taken to help.

Eddiesvoicebox

 

[Greg Truby]

Well you could certainly list them all in code, probably using a Case structure like...

Sub Example()
    Select Case dtmMyVariable
        Case #1/1/2005#, #2/1/2005#
            ' ...
        Case Else
            ' ...
    End Select
End Sub

Note: the above code in the US would equate to Jan 1 & Feb 1, not sure for UK/International settings.

But you might be better off putting the list in a range on a hidden worksheet and doing a Find or Vlookup or some other lookup type function against the list range.

Is there nothing these dates have in common? You can't test for 1st day of the month or "Is it a Friday" or something?

 

[Oaktree]

Have you looked into MS query? Go to data --> import external data --> new database query.

That's probably easier than using VBA.

 

[eddiesvoicebox]

I did think about MS query but someone else spent ages writing the code to run Access which does save valuable time and cuts the process down to 10 mins instead of 90 mins. Generally i want to run different code at weekends but also bank holidays so i cant really specify.

BTW thanks for your help so far