Comparing 2 comma delimited lists

SidFishes
SidFishes used Ask the Experts™
on
I have 2 lists coming from an ini file

Jim,Joe,Bob,Frank
 
2891,1284,1964,1999

these are user name and PIN lists (non-critical so in-clear pwd's is ok for me)

A user selects their name and enters their PIN. I want to check name against PIN to allow code to continue

I know i can get the position of the user name

vfinduserpos = InStr(1, vUserList, UserName)

bob = 9

but I can't figure out a way to find the position of bob's pin and read it.

x = mid(vuserpins,vfinduserPos,4)

 doesn't work because names & PINs may be different lengths so I get things like 4,19

So - how can I find position of word in one list, use that position to look up the same word position in another list?

Regex perhaps?


(btw I suck at regex so please be gentle)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Sid,

 I would use SPLIT() on both strings to convert them to one dimensional arrays, then walk the name array till you find the name (0 to UBound()).

 Once you have the element, then the PID is the same element in the other array.

Jim.
Top Expert 2016
Commented:
use Split() function

dim vUser, j, vName

vName=split(UserName,",")

for j=0 to ubound(vName)
        if vName(j)="Bob" then
           exit for
      end if
next

vUser=split(vUserList,",")(j)   '<<< this is equivalent

Commented:
Private Sub Command1_Click()
    vUserpins = "2891,1284,1964,1999"
    ole2 = Split(vUserpins, ",")
    vfinduserPos = 4
    Print ole2(vfinduserPos - 1)
End Sub
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

This is what I ended up using.
 
Dim vUser, j, k, aName, aPIN, vPIN
aName = Split(vUserList, ",")
For j = 0 To UBound(aName)
        If aName(j) = UserName Then
           Exit For
      End If
Next
vUser = Split(vUserList, ",")(j)
aPIN = Split(vUserPins, ",")
vPIN = aPIN(j)
If vPIN = UserPin Then
    MsgBox "ok"
Else
    MsgBox "Incorrect PIN"
End If

Commented:
This is a little Function :

Const vUserpins = "2891,1284,1964,1999"

Private Sub Command1_Click()
    m = myFunc(vUserpins, 3) ' ====>   returns 1964
    MsgBox m
End Sub
 
Function myFunc(ByVal L2 As String, ByVal L3 As Integer)
     ole2 = Split(L2, ",")
     myFunc = ole2(L3 - 1)
End Function
Splitting points because Jim was correct and first but my vba chops are pretty rusty. I would have got there but cap's code saved me some time. thx both

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial