Add formula for multiple recurrence to VB Code

[manju 3]

Hi Team, I have 3 columns A,C and F. Based on A and C, i am writing the formula for adding F cells together in the Output cell. I have the formula written for output column as

=IF($A1=0,F1,
IF($A1=1,IF(C1=4,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4,
IF(C1=3,F1 &CHAR(10)& F2 &CHAR(10)& F3,
IF(C1=2,F1 &CHAR(10)& F2,""))),""))

Its working fine as below:

A C F output
0 1 a a
1 2 b bc
2 2 c
1 3 d def
2 3 e
3 3 f

Can someone help me how can i add the formula in loop to check for C1>4 like and go on for if more values :

IF($A1=1,IF(C1=5,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5,
IF($A1=1,IF(C1=6,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6,
IF($A1=1,IF(C1=7,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6 &CHAR(10)& F7,

The VB Code shows below:

Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1=0,RC[-5],IF(RC1=1,IF(RC[-7]=4,RC[-5]&CHAR(10)&R[1]C[-5]&CHAR(10)&R[2]C[-5]&CHAR(10)&R[3]C[-5],IF(RC[-7]=3,RC[-5]&CHAR(10)&R[1]C[-5]&CHAR(10)&R[2]C[-5],IF(RC[-7]=2,RC[-5]&CHAR(10)&R[1]C[-5],""""))),""""))"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J262"), Type:=xlFillDefault
Range("J5:J262").Select

. I have written my own formula(function) but confused how can i assign that function to this cell as this the output excel and cannot add/insert as module and call from cell: Its working fine when tested in excel individually but when i tried to change the VB code and replace the function its not working: Here is the below code:

Range("J5").Select
ActiveCell.FormulaR1C1 = "Lookup_concat(J5,C2:C6,D2:D6)"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J262"), Type:=xlFillDefault
Range("J5:J262").Select

Can anyone suggest any solution for me how can i assign this function to the cell : The formula written is :

Option Explicit

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next

If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)