instr search

[Anonymous]

Is there a better way to write the following code?

If InStr(DescText, "PL") <> 0 Then
MatText = "AS3678" & Chr(13) + Chr(10) & "250"
ElseIf InStr(DescText, "FL") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "UB") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "UC") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "PFC") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "EA") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "UA") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "TFB") <> 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "SHS") <> 0 Then
MatText = "AS1163"
ElseIf InStr(DescText, "CHS") <> 0 Then
MatText = "AS1163"
ElseIf InStr(DescText, "RHS") <> 0 Then
MatText = "AS1163"
Else: MsgBox "No material"
MatText = ""
End If

Regards,

Simon

[Anonymous]

Hi Simon,

Here's another way of doing the same thing. Whether it's better I
wouldn't know without testing to see which runs faster.

I think it is easier to understand.


If InStr(DescText, "PL") > 0 Then
MatText = "AS3678" & Chr(13) + Chr(10) & "250"
ElseIf InStr(DescText, "FL") > 0 _
Or InStr(DescText, "UB") > 0 _
Or InStr(DescText, "UC") > 0 _
Or InStr(DescText, "PFC") > 0 _
Or InStr(DescText, "EA") > 0 _
Or InStr(DescText, "UA") > 0 _
Or InStr(DescText, "TFB") > 0 Then
MatText = "AS3679.1" & Chr(13) + Chr(10) & "300"
ElseIf InStr(DescText, "HS") > 0 Then
MatText = "AS1163"
Else
MsgBox "No material"
MatText = ""
End If




Regards


Laurie Comerford


smjgsmith wrote:
> Is there a better way to write the following code? If InStr(DescText,
> "PL") <> 0 Then MatText = "AS3678" & Chr(13) + Chr(10) & "250" ElseIf
> InStr(DescText, "FL") <> 0 Then MatText = "AS3679.1" & Chr(13) + Chr(10)
> & "300" ElseIf InStr(DescText, "UB") <> 0 Then MatText = "AS3679.1" &
> Chr(13) + Chr(10) & "300" ElseIf InStr(DescText, "UC") <> 0 Then MatText
> = "AS3679.1" & Chr(13) + Chr(10) & "300" ElseIf InStr(DescText, "PFC")
> <> 0 Then MatText = "AS3679.1" & Chr(13) + Chr(10) & "300" ElseIf
> InStr(DescText, "EA") <> 0 Then MatText = "AS3679.1" & Chr(13) + Chr(10)
> & "300" ElseIf InStr(DescText, "UA") <> 0 Then MatText = "AS3679.1" &
> Chr(13) + Chr(10) & "300" ElseIf InStr(DescText, "TFB") <> 0 Then
> MatText = "AS3679.1" & Chr(13) + Chr(10) & "300" ElseIf InStr(DescText,
> "SHS") <> 0 Then MatText = "AS1163" ElseIf InStr(DescText, "CHS") <> 0
> Then MatText = "AS1163" ElseIf InStr(DescText, "RHS") <> 0 Then MatText
> = "AS1163" Else: MsgBox "No material" MatText = "" End If Regards, Simon

[Anonymous]

Thanks Laurie

[Anonymous]

I like Lauries, but heres another way that uses a search of safearrays:

Sub TEST()
MsgBox MatText("PL01")
End Sub

Function MatText(ByVal DescText As String)
Const ASMax As String = "AS3679.1" & vbCrLf & "300"
SearchStr = Array("PL", "FL", "UB", "UC", "PFC", "EA", "UA", "TFB", "SHS", "CHS", "RHS")
ReportStr = Array("AS3678" & vbCrLf & "250", ASMax, ASMax, ASMax, _
ASMax, ASMax, ASMax, ASMax, "AS1163", "AS1163", "AS1163")
For I% = 0 To UBound(SearchStr):
If InStr(DescText, SearchStr(I%)) Then MatText = ReportStr(I%): Exit For:
Next I%
End Function