Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match and Copy to array
I have a question about som things.
Earlier i get this script, and it works fine. Sub test() Dim r As Range, txt, ws1 As Worksheet, ws2 As Worksheet Dim LookUpCell As Range, x Set ws1 = Sheets("FireWall Rules"): Set ws2 = Sheets("LDAP") With ws2 For Each r In .Range("b1", .Range("b65536").End(xlUp)) If Not IsEmpty(r) Then If InStr(r, ";") = 0 Then Set LookUpCell = ws1.Range("a:a").Find(what:=r.Value, lookat:=xlWhole) If Not LookUpCell Is Nothing Then LookUpCell.Offset(, 1) = r.Offset(, -1).Value End If Else txt = Split(Replace(r, " ", ""), ";") For Each x In txt Set LookUpCell = ws1.Range("a:a").Find(what:=x, lookat:=xlWhole) If Not LookUpCell Is Nothing Then LookUpCell.Offset(, 1) = r.Offset(, -1).Value End If Next End If End If Next Set ws1 = Nothing: Set ws2 = Nothing: Erase txt End With End Sub This script looks in Column A of sheet1 for a certain company name, search for the same company name in column B of sheet2, and copy the column A value of the same row to sheet1 Column B. It works perfect. Here a little example of sheet 2 A B C D E 1 employee Company 2 Steve Sony 3 John Philips 4 Chris Sony 5 Steven Sony 6 Rutger Philips As you can see there are more employees at one company (company names are also represented in column A of sheet but without dupes). I want the empleyees in array, cause these have to be matched with my third sheet.... I don't know how to do this any suggestions? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH() on a 2D array? | Excel Discussion (Misc queries) | |||
Match using array of column and row references to match with | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Copy Array pointer rather than entire array | Excel Programming |