Array Problems

[jjpski]

Been a little while and my VBA is rusty.

I have data in sheet 1 column M and need to compare it to data in Sheet 2 column A.

Once a match is found I need the data for that row from sheet 1 column A to populate sheet Column B.
Sheet 1 Column M has 8500 rows and sheet 2 column A has 160.

I think the problem I am running into is the data in column M is MAC addresses.

Sheet 1

Column A	Column M
52AAAAAA-AAAAA	00:00:00:00:00:08
52AAAAAA-AAAAB	00:00:00:00:00:09
52AAAAAA-AAAAC	00:00:00:00:00:11
52AAAAAA-AAAAD	00:00:00:00:00:14

<tbody>
</tbody>

Sheet 2

Column A	Column B
00:00:00:00:00:08	Data from sheet 1 Column A
00:00:00:00:00:07
00:00:00:00:00:10
00:00:00:00:00:09

<tbody>
</tbody>

Thanks for the help!

Jay

 

[hiker95]

jjpski,

Sample worksheets:

Excel 2007
	A	M
1	52AAAAAA-AAAAA	00:00:00:00:00:08
2	52AAAAAA-AAAAB	00:00:00:00:00:09
3	52AAAAAA-AAAAC	00:00:00:00:00:11
4	52AAAAAA-AAAAD	00:00:00:00:00:14
5
Sheet1

Excel 2007
	A	B
1	00:00:00:00:00:08
2	00:00:00:00:00:07
3	00:00:00:00:00:10
4	00:00:00:00:00:09
5
Sheet2

After the macro in Sheet2:

Excel 2007
	A	B
1	00:00:00:00:00:08	52AAAAAA-AAAAA
2	00:00:00:00:00:07
3	00:00:00:00:00:10
4	00:00:00:00:00:09	52AAAAAA-AAAAB
5
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetMACAddress()
' hiker95, 12/20/2014, ME825353
Dim c As Range, a As Range
Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").Range("M1", Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp))
  Set a = Sheets("Sheet2").Columns(1).Find(c.Value, LookAt:=xlWhole)
  If Not a Is Nothing Then
    Sheets("Sheet2").Range("B" & a.Row).Value = Sheets("Sheet1").Range("A" & c.Row).Value
  End If
Next c
With Sheets("Sheet2")
  .Columns(2).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMACAddress macro.

 

[Peter_SSs]

.. possible alternative:

Sub GetIt()
  With Sheets("Sheet2").Range("B1:B" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IFERROR(INDEX(Sheet1!A:A,MATCH(A1,Sheet1!M:M,0)),"""")"
    .Value = .Value
  End With
End Sub

 

[jjpski]

Hiker95

I get a subscript out of range on this line. I ran into the same error with some other coding. The data written is by a VBS script.

For Each c In Sheets("Sheet1").Range("M1", Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp))

Thanks again.

jjpski

 

[Peter_SSs]

Hiker95

I get a subscript out of range on this line. I ran into the same error with some other coding. The data written is by a VBS script.

For Each c In Sheets("Sheet1").Range("M1", Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp))

Thanks again.

jjpski

That would normally indicate that you don't have a sheet in the Active Workbook called "Sheet1". Check the name, in particular that there are not spaces before or after the text in the sheet name.


Similar error from my code? Did you try it?

 

[jjpski]

Peter,

Yes I did get yours to work and I will try your suggestion on the sheet reference.

Jay

 

[jjpski]

Peter,


Your suggestion on the sheet reference worked. It is working just fine. Thanks for knowledge.

Jay

 

[Peter_SSs]

No problem, glad to help.

 

[hiker95]

jjpski,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.