Complicated VBA IF Statement

[drrocket5292]

Hi,
I've got 4 separate yet related text files that my company uses that I've written macros in excel for to parse out the information from the text file to an excel file. After the file is parsed, the macro grabs the value of a certain cell and tests it against some criteria (thats different for each spreadsheet), returns a string value based on which criteria is met and then XRefs that string value against an XRef worksheet that I've built and returns the value I need. It all works totally fine but I dont like having this all hardcoded into my macro and was looking for ideas on how to build a function and worksheet that would return the value from the XRef worksheet that I need.

The main problems that I have are that the criteria relates to cell values that are in different locations on each of the spreadsheets, for instance, on this spreadsheet one of the criterias is the value of a cell in column AE but on another spreadsheet that same value is located in column AA. Also, the criteria for all of this is really complicated and confusing and based on what seems to be the random logic of the company that sends us the file. Any suggestions on this would be greatly appreciated. Here's my macro:


Range("AD" & iCurrentRow).Value = Trim(Mid(rngCurrentCell.Value, 35, 12))
sNFSProductCode = Range("AD" & iCurrentRow).Value

If Right(sNFSProductCode, 1) = "Y" Then
sNFSProductType = "CMOS"
ElseIf sNFSProductCode = "SDMC" Then
sNFSProductType = "SDMC"
ElseIf Left(sNFSProductCode, 4) = "SECC" And Trim(Range("N" & iCurrentRow).Value) <> "C" Then
sNFSProductType = "SECC"
ElseIf Left(sNFSProductCode, 4) = "SECC" Then
sNFSProductType = "SECCLE"
ElseIf Left(sNFSProductCode, 5) = "SECFA" And Range("AE" & iCurrentRow).Value = 1 Then
sNFSProductType = "SECFA1"
ElseIf Left(sNFSProductCode, 5) = "SECFA" And Range("AE" & iCurrentRow).Value = 2 Then
sNFSProductType = "SECFA2"
ElseIf Left(sNFSProductCode, 5) = "SECFA" And Range("AE" & iCurrentRow).Value = 4 Then
sNFSProductType = "SECFA4"
ElseIf Left(sNFSProductCode, 5) = "SECFA" And Range("AE" & iCurrentRow).Value = 8 Then
sNFSProductType = "SECFA8"
ElseIf Left(sNFSProductCode, 5) = "SECFC" And Range("AE" & iCurrentRow).Value = 1 Then
sNFSProductType = "SECFC1"
ElseIf Left(sNFSProductCode, 5) = "SECFC" And Range("AE" & iCurrentRow).Value = 2 Then
sNFSProductType = "SECFC2"
ElseIf Left(sNFSProductCode, 5) = "SECFC" And Range("AE" & iCurrentRow).Value = 4 Then
sNFSProductType = "SECFC4"
ElseIf Left(sNFSProductCode, 5) = "SECFC" And Range("AE" & iCurrentRow).Value = 8 Then
sNFSProductType = "SECFC8"
ElseIf Left(sNFSProductCode, 5) = "SECFD" And Range("AE" & iCurrentRow).Value = 1 Then
sNFSProductType = "SECFD1"
ElseIf Left(sNFSProductCode, 5) = "SECFD" And Range("AE" & iCurrentRow).Value = 2 Then
sNFSProductType = "SECFD2"
ElseIf Left(sNFSProductCode, 5) = "SECFD" And Range("AE" & iCurrentRow).Value = 4 Then
sNFSProductType = "SECFD4"
ElseIf Left(sNFSProductCode, 5) = "SECFD" And Range("AE" & iCurrentRow).Value = 8 Then
sNFSProductType = "SECFD8"
ElseIf Left(sNFSProductCode, 5) = "SEPFA" Then
sNFSProductType = "SEPFA"
ElseIf Left(sNFSProductCode, 5) = "SRWFD" Then
sNFSProductType = "SRWFD"
ElseIf Left(sNFSProductCode, 4) = "SECF" Then
sNFSProductType = "SECF"
ElseIf Left(sNFSProductCode, 4) = "SEPF" Then
sNFSProductType = "SEPF"
ElseIf sNFSProductCode = "SEC" And Range("AE" & iCurrentRow).Value < 4 Then
sNFSProductType = "SECLE"
ElseIf sNFSProductCode = "SEP" And Range("AE" & iCurrentRow).Value < 4 Then
sNFSProductType = "SEPLE"
ElseIf sNFSProductCode = "SRW" And Range("AE" & iCurrentRow).Value < 4 Then
sNFSProductType = "SRWLE"
ElseIf sNFSProductCode = "SEC" Then
sNFSProductType = "SECOTC"
ElseIf sNFSProductCode = "SEP" Then
sNFSProductType = "SEPOTC"
ElseIf sNFSProductCode = "SRW" Then
sNFSProductType = "SRWOTC"
ElseIf Left(sNFSProductCode, 6) = "SDBSAR" Then
sNFSProductType = "SDBSAR"
ElseIf Left(sNFSProductCode, 6) = "SDBSAP" Then
sNFSProductType = "SDBSAP"
ElseIf sNFSProductCode = "SDBC" And Trim(Range("N" & iCurrentRow).Value) = "F" _
And Range("M" & iCurrentRow).Value = 0 Then
sNFSProductType = "SDBCF"
ElseIf Left(sNFSProductCode, 4) = "SDBC" And Range("L" & iCurrentRow).Value < 4 Then
sNFSProductType = "SDBCLC"
ElseIf Left(sNFSProductCode, 4) = "SDBC" Then
sNFSProductType = "SDBCOTC"
ElseIf sNFSProductCode = "SUL" Then
sNFSProductType = "SUL"
ElseIf Len(sNFSProductCode) > 2 And Left(sNFSProductCode, 2) = "SU" And _
Asc(Right(sNFSProductCode, 1)) > 76 And Asc(Right(sNFSProductCode, 1)) < 85 Then
sNFSProductType = "SULM"
ElseIf Left(sNFSProductCode, 4) = "SDBM" Then
sNFSProductType = "SDBM"
ElseIf Left(sNFSProductCode, 3) = "SEM" Then
sNFSProductType = "SEM"
ElseIf Left(sNFSProductCode, 3) = "SRO" Then
sNFSProductType = "SRO"
ElseIf Left(sNFSProductCode, 3) = "SRR" Then
sNFSProductType = "SRR"
ElseIf Left(sNFSProductCode, 5) = "SDBST" Then
sNFSProductType = "SDBST"
ElseIf Left(sNFSProductCode, 3) = "SUU" Then
sNFSProductType = "SUU"
ElseIf Left(sNFSProductCode, 1) = "U" Then
sNFSProductType = "U"
ElseIf Left(sNFSProductCode, 4) = "SDMP" Then
sNFSProductType = "SDMP"
Else
Rows(iCurrentRow).EntireRow.Interior.ColorIndex = 40
End If

Set rngFoundCell = rngProductCodeXRefColumn.Find(what:=sNFSProductType, LookIn:=xlValues, lookat:=xlWhole)

If Not rngFoundCell Is Nothing Then
Range("AQ" & iCurrentRow).Value = rngFoundCell.Offset(0, 1).Value
Else
Rows(iCurrentRow).EntireRow.Interior.ColorIndex = 38
End If

 

[JoeriMJ]

Have you tried setting up a SELECT clause? it seems doable when combined with a for statement. If you group the number indices from the left statements. you'll probably still have to use a few if statements but i think it will surely clarify your code.

 

[Rofeu]

Hi,


for the most part, you might set up an array and loop through it:

Dim GetCode([NumberofMembers]) As Variant
Dim sNFSProductType As String

sNFSProductType = "SEPFA"

GetCode(0) = Array(4, "SECC", "SECCLE")
GetCode(1) = Array(5, "SEPFA", "SEPFA4")
etc.

For I = LBound(GetCode) To UBound(GetCode)
If Left(sNFSProductType, GetCode(I)(0)) = GetCode(I)(1) Then sNFSProductType = GetCode(I)(2)
Next

You could handle the more complex ones after that with a select case.


Instead of Range("AE" & iCurrentRow).Value you could do something like:

Dim ColNum as integer

Select Case YourSheet
case "Sheet1"
ColNum = 26
Case "Sheet2"
ColNum = 28
End Select

and then use

YourSheet.Cells(iCurrentRow, ColNum).Value

But to be quite honest, I'd set up a table with all the product codes and there corresponding values and just read from that. As you say, there's no apparent logic, then a table will be your best option IMHO.

Cheers,

Roel