Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup text in a string with wildcard and return a certain value | Excel Worksheet Functions | |||
lookup a value within a text string | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Lookup String in Text | Excel Programming | |||
LOOKUP a text string created from IF statement | Excel Discussion (Misc queries) |