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



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

Lookup Multiple text in String
 

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

Lookup Multiple text in String
 

ahhh...ok. Thnx Les :-)


--
okelly
------------------------------------------------------------------------
okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708
View this thread: http://www.excelforum.com/showthread...hreadid=565973

Lookup Multiple text in String
 

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" ¼¶¼g©ó¶l¥ó·s»D:okelly.2bnpmq_1154092205.4307@excel forum-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