Lookup Multiple text in String

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

Please Login or Register  to view this content.

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

--
Les Torchia-Wells


"okelly" wrote:

>
> 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
>
>
>
>
> --------------------
>
>
> --
> okelly
> ------------------------------------------------------------------------
> okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708
> View this thread: http://www.excelforum.com/showthread...hreadid=565973
>
>

ahhh...ok. Thnx Les :-)

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

"okelly" <okelly.2bnpmq_1154092205.4307@excelforum-nospam.com> ¼¶¼g©ó¶l¥ó·s»D:okelly.2bnpmq_1154092205.4307@excelforum-nospam.com...
>
> 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
>
>
>
>
> --------------------
>
>
> --
> okelly
> ------------------------------------------------------------------------
> okelly's Profile:
> http://www.excelforum.com/member.php...o&userid=36708
> View this thread: http://www.excelforum.com/showthread...hreadid=565973
>