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