Modify a Function to add an extra condition

Hi,

I want to modify the function in the code below to test 2 variables instead of just the 1.

So, as an example, in the first IF statement, the string looks for "CAMP MIRAGE BASED UNITS/TACTICAL AIRLIFT UNIT" and if found it returns "A - CM - TAU".

I would like this IF statement to look for "CAMP MIRAGE BASED UNITS/TACTICAL AIRLIFT UNIT" and a date in the same row (column N) (example 02 Sep 2009) before giving it's return.

Function findCode(strIn As String) As String


    If InStr(strIn, "CAMP MIRAGE BASED UNITS/TACTICAL AIRLIFT UNIT") > 0 Then
    findCode = "A – CM – TAU"
    Exit Function
    End If

    If InStr(strIn, "CAMP MIRAGE BASED UNITS/THEATRE SUPPORT ELEMENT") > 0 Then
        findCode = "B – CM TSE"
    Exit Function
    End If

    If InStr(strIn, "KABUL and OTHER LOCATION UNITS/MISC HQ and STAFFS") > 0 Then
        findCode = "C – MISC HQ & STAFF"
    Exit Function
    End If

    If InStr(strIn, "KABUL and OTHER LOCATION UNITS/SLTC – A") > 0 Then
        findCode = "Nothins specified"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/ASIC") > 0 Then
        findCode = "D – ASIC"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/AVN COY") > 0 Then
        findCode = "Nothins specified"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/ENGR SUPPORT UNIT") > 0 Then
        findCode = "E – ENGR SP"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/HSS UNIT") > 0 Then
        findCode = "F – HSS"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/INF BG") > 0 Then
        findCode = "G – INF BG"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/JTF AFG HQ") > 0 Then
        findCode = "H – JTF AFG HQ BLK 1"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/MP COY") > 0 Then
        findCode = "I – MP COY"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/NSE") > 0 Then
        findCode = "J – NSE"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/OMLT") > 0 Then
        findCode = "K – OMLT"
    Exit Function
    End If

    If InStr(strIn, "KANDAHAR BASED UNITS/PRT") > 0 Then
        findCode = "L – PRT"
    Else
        findCode = "No Code Specified"
    End If

    
End Function

Thank You

TB

Hi king ... first, can I suggest this to replace the structure of your existing set of sequential If statements?

Function findCode(strIn As String) As String
Dim varTitles As Variant, varCodes As Variant
Dim i As Integer
    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")
    varCodes = Array("A – CM – TAU", "B – CM TSE", "C – MISC HQ & STAFF")
    Select Case strIn
        For i = LBound(varTitles) To UBound(varTitles)
            Case intstr(strIn, varTitles(i)) > 0
                findCode = varCodes(i)
                Exit Function
            Case Else
                'trap if not found a match:
        Next i
    End Select
End Function

You'll need to fill in the rest of your titles and codes in the arrays ... I actually suggest you do this as a public constant so it's not set everytime the function is called .. doesn't make much difference, but it's not technically efficient or needed.

As for adding the 2nd condition, there is nothing in there that identifies a current cell or references a cell in a loop, so you'll either need to pass it in or do it outside the function. If I understand what you're asking though, shouldn't you only call the function in the first place if there is a date in column N of the current row? If there isn't ... then don't call the function at all. Let me know if I've misunderstood this last bit though:-)

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.