Match Array to Column

ScotTFO

Board Regular
Joined
May 30, 2008
Messages
72
Just wondering what the best process of this would be.

Originally I was trying to write the array out into another sheet then do matching, but that was a very very slow and seemed like a redunant process if the values are already stored in an array.

What I am trying to do is I have column A with a bunch of numbers in it.

548543
45435
459873453
43598743

Then I have an array with info in it.

The array is myobj

For Each user In myobj

I am trying to compare.
user.Name to column A.
Then write user.FullName is column B in that same row.

What would be the best method of doing this?

Thanks!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Assuming your username list is on Sheet1

Code:
For Each user In myobj
 rw = WorksheetFunction.Match(user.Name,Sheets("Sheet1").Columns(1),0)
 Sheets("Sheet1").Cells(rw,2) = user.FullName
Next user

You may need a handler if you're returning a user from the active directory that is NOT listed in Column A on Sheet1.
 
Upvote 0
That looks 1000 times more efficient than what I was doing, what would the best error handler for this be?

I put a:
For Each user In myobj
On Error Resume Next
rw = WorksheetFunction.Match(user.Name, Sheets("Sheet1").Columns(1), 0)
Sheets("Sheet1").Cells(rw, 2) = user.FullName
Next user

The code seems to be running but none of the info is writing in the second column.
Also I know that there are matches because if I do:

'For Each user In myobj
' Cells(i, 3) = user.Name
' Cells(i, 4) = user.FullName
' i = i + 1
'Next

It all shows up correctly
 
Upvote 0
If you just want to ignore missing users On Error Resume Next is fine but you should reset the handler after you're done with the loop to default using

On Error GoTo 0

such that if remaining code bugs out you're notified and the code halts...

When you just return the values can you then in turn use MATCH function to see if native XL can find the returned username in column A?

Just to be sure--- if you're existing username list is not on tab called "Sheet1" you need to change the code to reflect the actual sheet name.
 
Upvote 0
Hrmm..it all looks correct, no doubt this will be some stupid bug of my own doing, but this is just driving me crazy.

Code:
    For Each user In myobj
        On Error Resume Next
        rw = WorksheetFunction.Match(user.Name, Sheets("Sheet1").Columns(1), 0)
        Sheets("Sheet1").Cells(rw, 2) = user.FullName
        On Error GoTo 0
    Next user
And it is in Sheet1

I am sure I will eventually figure out why, but thank you very much for the code, I'll be using it once I figure out where I am going wrong.
 
Upvote 0
just a pointer -- move the On Error GoTo 0 after Next User rather than before.

if you simply return the data to say C1, D1 etc... and in E1 you enter = MATCH(C1,A:A,0)
and repeat for all values returned in C1/D1 do you get numbers or errors in E ?

EDIT: may have to do with NUMBERS as text etc... are values in A numeric or text ? If numeric try changing the match to

Code:
.Match(0+user.name,Sheets("Sheet1").Columns(1),0)

If the opposite is true (ie A is text and routine returning numbers (user.Name)) you could convert the user.name to Str
Code:
.Match(CStr(user.name),Sheets("Sheet1").Columns(1),0)
 
Last edited:
Upvote 0
This is what I ended up using and it worked.

Code:
Set rw= Range("A:A").Find(user.Name, LookIn:=xlValues, LookAt:=xlWhole)
If Not rwIs Nothing Then
   rw.Offset(0, 1).Value = user.FullName
End If
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top