Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Multiple Occurrence Of Item In Array
Alan, Thank you for your help. I was using C+S+E while still in the functio dialog box. After closing it, I did F2 and then the C+S+E again and i worked. I am now trying to write a macro to run once for each unique employe and paste in the phone numbers. I can't get the macro to enter th array as an array. What happens is the problem I originally had. I enters the formula without the brackets. I then have to manually ente the array for it to work. Below is the code I wrote and the results. Sub ENTEROFFICEandFANUMBERSINTOPHONELIST() Dim currentname As String Dim howmanytimes As Integer Range("A2").Select Range(Selection, Selection.End(xlDown)).Select howmanytimes = Selection.Rows.Count Range("A2").Select For a = 1 To howmanytimes ActiveCell.Select Start = ActiveCell.name currentname = ActiveCell.Value Range("M1").Value = currentname Range("N1:R1").Select Selection.FormulaArray = "=PERSONAL.XLS!ArrayMatch(RC[-1],NAME)" Range("M2").FormulaR1C1 = _ "=IF(ISERROR(INDEX(OFFICE_AND_FA_NUMBER,R[-1]C[1])),"""",INDEX(OFFICE_AND_FA_NUMBER,R[-1]C[1]))" Range("M3").FormulaR1C1 = _ "=IF(ISERROR(INDEX(OFFICE_AND_FA_NUMBER,R[-2]C[2])),"""",INDEX(OFFICE_AND_FA_NUMBER,R[-2]C[2]))" Range("M4").FormulaR1C1 = _ "=IF(ISERROR(INDEX(OFFICE_AND_FA_NUMBER,R[-3]C[3])),"""",INDEX(OFFICE_AND_FA_NUMBER,R[-3]C[3]))" Range("M5").FormulaR1C1 = _ "=IF(ISERROR(INDEX(OFFICE_AND_FA_NUMBER,R[-4]C[4])),"""",INDEX(OFFICE_AND_FA_NUMBER,R[-4]C[4]))" Range("M6").FormulaR1C1 = _ "=IF(ISERROR(INDEX(OFFICE_AND_FA_NUMBER,R[-5]C[5])),"""",INDEX(OFFICE_AND_FA_NUMBER,R[-5]C[5]))" If m2 < "" And m3 < "" And m4 < "" And m5 < "" And m6 < " Then Range("M2:m6").Select GoTo 50 End If If m2 < "" And m3 < "" And m4 < "" And m5 < "" Then Range("M2:m5").Select GoTo 50 End If If m2 < "" And m3 < "" And m4 < "" Then Range("M2:m4").Select GoTo 50 End If If m2 < "" And m3 < "" Then Range("M2:m3").Select GoTo 50 End If If m2 < "" Then Range("M2").Select GoTo 50 Else GoTo 100 End If 50 Selection.Copy Range(Start).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False 100 Range(Start).Select ActiveCell.Offset(1, 0).Select Next a End Sub RESULTS Irv Becker #VALUE! #VALUE! #VALUE! #VALUE! #VALUE -- John Sharke ----------------------------------------------------------------------- John Sharkey's Profile: http://www.excelforum.com/member.php...fo&userid=1588 View this thread: http://www.excelforum.com/showthread.php?threadid=27369 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP VALUE OCCURS ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE | Excel Worksheet Functions | |||
Multiple Item Lookup | Excel Worksheet Functions | |||
Lookup Multiple Occurrence Of Item In Array | Excel Programming | |||
Lookup Multiple Occurrence Of Item In Array | Excel Programming | |||
Lookup Multiple Occurrence Of Item In Array | Excel Programming |