Compacting or referencing code

[Thompyt]

I have found an ungraceful means to getting the code to output the data format as I want. I don't like the way it has to repeat itself for the same code set. How do I make a reference to it in 1 place? A module? I tried to use 1 instance in the code, before the start of the If sequence that includes the below code, but the first time it uses the data it outputs the incorrect information.



INF BN CO A output = 2100 A INF BN 1 Where the first time A ="" and not 1, B=1. After it runs through the complete set A - D, then it will start acting right.

ElseIf (rU("SRW  " & I)) Like "* CO" Then
            BN = Left(rU("SRW  " & I), InStr(rU("SRW  " & I), "O") - 5)
            CO = Replace(Mid(rU("SRW  " & I), InStrRev(rU("SRW  " & I), "O") - 3), " CO", "")
            
            If CO = "A" Then
                CO1 = 1
            ElseIf CO = "B" Then
                CO1 = 2
            ElseIf CO = "C" Then
                CO1 = 3
            ElseIf CO = "D" Then
                CO1 = 4
            ElseIf CO = "E" Then
                CO1 = 5
            ElseIf CO = "F" Then
                CO1 = 6
            ElseIf CO = "HHB" Then
                CO1 = "A"
             ElseIf CO = "HHC" Then
                CO1 = "A"
            ElseIf CO = "HHT" Then
                CO1 = "A"
            End If

                rU("SRW" & I) = 2 & Z & CO1 & "00-" & CO & " " & Base & S
    
    ElseIf Not rU("SRW  " & I) Like "* HQ * PLT" And rU("SRW  " & I) Like "* ? # PLT" Then
            BN = Left(rU("SRW  " & I), InStr(rU("SRW  " & I), "T") - 7)
            CO = Right(Left(rU("SRW  " & I), InStrRev(rU("SRW  " & I), "T") - 6), 1)
            PL = Replace(Mid(rU("SRW  " & I), InStr(rU("SRW  " & I), "T") - 4), " PLT", "")
            
            If CO = "A" Then
                CO1 = 1
            ElseIf CO = "B" Then
                CO1 = 2
            ElseIf CO = "C" Then
                CO1 = 3
            ElseIf CO = "D" Then
                CO1 = 4
            ElseIf CO = "E" Then
                CO1 = 5
            ElseIf CO = "F" Then
                CO1 = 6
            ElseIf CO = "HHB" Then
                CO1 = "A"
             ElseIf CO = "HHC" Then
                CO1 = "A"
            ElseIf CO = "HHT" Then
                CO1 = "A"
            End If
            
                rU("SRW" & I) = 2 & Z & CO1 & PL & PL & "-" & PL & " " & CO & " " & Base & S

[davegri]

Replace the blue with

CO1=fcnCOgen(C0)

Then in the same module add UDF

Private function fcnCOgen(arg as string) as string
    dim sRslt as string    
    select case arg
        case "A"
            sRslt = 1
        case "B"
            sRslt = 2
        case "C"
            sRslt = 3
.
.
.
        case "HHT"
            sRslt = "A"
    end select
    fcnCOgen = sRslt
end function

[Thompyt]

Thanks! I moved it to its own separate module. How do you reference 2 items in one select case?

dim sRslt as string, nrslt as integer
    select case arg
        case "A"
            sRslt = "A "
            NRslt = 1
        case "B"
    fcnCOgen = sRslt
    fcnCOgen1 = NRslt?

[davegri]

Perhaps another UDF (fcnCOgen1) that returns a long instead of a string.
Then call the one that will return the expected string or number.

[Micron]

Or use the same function but pass and return a variant.
Private function fcnCOgen(arg as Variant) as Variant

From what I can gather, you don't need to pass a parameter to it that defines which you want so that you can coerce the data type within the custom function. It might be as simple as

select case arg
        case "A"
            sRslt = fcnCOgen, "A "
            NRslt = fcnCOgen, 1

        case "B"

BTW, I think you have this backwards. You're making the function = sRslt, not the other way around.
fcnCOgen = sRslt