Lookup Multiple text in String

[okelly]

Hi folks
Can you provide me the correct syntax for the "AND" command below.
ie If "HP" is found in one field and "DL380" is found in another field
then this record must relate to a "HP Proliant DL380" server etc
etc...

Thanks
Conor



Code:
--------------------


Function Server(pVal As String, pVal2 As String) As String

If InStr(pVal, "DL380") AND (pVal2, "HP") Then
Server = "HP Proliant DL380"

ElseIf InStr(pVal, "DL340") AND (pVal2, "HP") Then
Server = "HP Proliant DL340"

ElseIf InStr(pVal, "DL580") AND (pVal2, "HP") Then
Server = "HP Proliant DL580"

Else
ServerType = "Hardware Not Defined"
End If

End Function

 

[Les]

Simply repeat the function
e.g

Function Server(pVal As String, pVal2 As String) As String

If InStr(pVal, "DL380") AND InStr(pVal2, "HP") Then
Server = "HP Proliant DL380"

ElseIf InStr(pVal, "DL340") AND InStr(pVal2, "HP") Then
Server = "HP Proliant DL340"

ElseIf InStr(pVal, "DL580") AND Instr(pVal2, "HP") Then
Server = "HP Proliant DL580"

Else
ServerType = "Hardware Not Defined"
End If

End Function

 

[okelly]

ahhh...ok. Thnx Les

 

[NickHK]

How's this ?
Seemed easier like this, if you need to expand the range of
manufactures/models covered

Private Sub CommandButton1_Click()
MsgBox Server(" hhh DL580lklkl", "njjjjP ")
End Sub

Function Server(pVal As String, pVal2 As String) As String
Dim ServerModels As Variant
Dim i As Long
'Just a source for the array; could also be from a WS range
Const HPSeversModels As String = "DL380,DL340,DL580"
'Const IBMSeversModels As String = "IBM100,IBM200,IBM200"

'Assume failure
Server = "Hardware Not Defined"

If InStr(pVal2, "HP") > 0 Then

ServerModels = Split(HPSeversModels, ",")
For i = 0 To UBound(ServerModels)
If InStr(pVal, ServerModels(i)) > 0 Then Server = "HP Proliant " &
ServerModels(i)
Next
'ElseIf InStr(pVal2, "IBM") > 0 Then
'And another If with IBMServerModels
End If
End Function

NickHK