Modify a Function to add an extra condition

Hi,

Thank you for answering.

I will try to explain what I am trying to do. Please bear with me.

OK,

I have column A, which is varTitles in your code. When the code runs it will put the varCode results in column B. I am going to name some of the items in varCode slightly differently (ie JTf Blk I, JTF Blk II, etc depending on the date in column N). If column B does not reference column N then it will not give the results I am looiking for.

I thought some sort of IF...Then statement would work, such as If Column B is = "Test" & Column N = "6 Oct 2009" then "A-Help". This comparison is done on the same row for column B and column N.


I guess I would need an array for the varTitles, varCode and varDates?

I am not a programmer so if you know how to set up a framework for this, and I can fill in the data, it would be greatly appreciated.

I am trying to learn this VBA, but as I am sure you can appreciate it is not easy, especially if you do not have an aptitude for it (such as me)!!



Thank you for any help.

Well, without knowing the date conditions you're matching on (i.e. lots of specific dates or less than a particular date etc), the following will work, but may not be the most efficient way ... but I'm sure you can modify it to suit given the structure.

Sub YourMainSub()
    'code to when you call the function ...
    'I assume you have a loop or call the function on a particular cell;
    'this assumes calling on the activecell (or you could run a loop
    'on a range variable in exactly the same way); I also assume your
    '"title" string is in Col A and the date is in Col N:
    Call findCode(Range("A" & ActiveCell.Row), Range("N" & ActiveCell.Row))
    
    'etc etc ...
End Sub

Function findCode(strIn As String, datDate As Date) As String
Dim varTitles As Variant, varCodes As Variant
Dim i As Integer
    'as mentioned, I'd take these out of the function (no need to define & redefine them
    'everytime it's called) and declare them as global constants instead:
    varTitles = Array("CAMP MIRAGE BASED UNITS/TACTICAL AIRLIFT UNIT", "CAMP MIRAGE BASED UNITS/THEATRE SUPPORT ELEMENT", "KABUL and OTHER LOCATION UNITS/MISC HQ and STAFFS")
    varCodes1 = Array("A – CM – TAU", "B – CM TSE", "C – MISC HQ & STAFF")
    varcodes2 = Array("A – CM – TAU - 2", "B – CM TSE - 2", "C – MISC HQ & STAFF - 2")
    Select Case strIn
        For i = LBound(varTitles) To UBound(varTitles)
            Case intstr(strIn, varTitles(i)) > 0
                'I've assumed you'll assign one value if the date is before (say) 01 Jan 08,
                'and another if it's after then:
                If datDate < "01 Jan 08" Then
                    findCode = varCodes1(i)
                ElseIf datDate >= "01 Jan 08" Then
                    findCode = varcodes2(i)
                End If
                Exit Function
        Next i
    End Select
End Function

Of course, depending on how many variations in date/string combinations you have, you might consider a 2-dimensional code array to cater for this (rather than having too many individual single arrays); the primary index is the same as the other arrays and the secondary index is for each variation. Think of a 2-D array as a grid of rows & columns; for each "row" (corresponding to your activecell.row and therefore text string) you can have as many options of dates or codes as you like.

Hope that helps point you in the right direction.