Enter User Defined Function into Column Using VBA

[vgresia]

I currently am using a formula in Column J of Sheet 2 of my workbook that will look up values from 5 columns on Sheet 1 and return the corresponding text. For example if the value from column M on Sheet 2 matches any of the values from column J on Sheet 1 it would return "N", if not it would look in column K and if matched anything there it would return D, and so on. I am doing this in VBA so the formula used is
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">ActiveSheet.Range("J2:J" & intLastRow).FormulaR1C1 = _
"=IFERROR(IF(ISNUMBER(MATCH(RC[3],Sheet1!C10,0)),""N"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C11,0)),""D"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C12,0)),""R"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C13,0)),""G"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C14,0)),""F"",""""))))), """")"</code>This formula works well and fills in the corresponding values. I then created a user defined function that will look up all of the values in column J that are associated with an ID number found in column C and separate them by commas. This function also works well when entered into a cell.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Function get_areas(ID As String) As String
Dim rng As Range, cel As Range

Set rng = Range("A2:A" & Cells(rows.count,1).End(xlUp).Row)

Dim areas As String
For Each cel In rng
If IsNumeric(Left(cel, 1)) And cel.Offset(0, 2) = ID Then
If InStr(1, areas, cel.Offset(0, 9)) = 0 Then
areas = cel.Offset(0, 9) & ", " & areas
End If
End If
Next cel

areas = Trim(Left(areas, Len(areas) - 2))
get_areas = areas
End Function</code>Ideally, what I would like to do is run the original formula in all cells in column J that DON'T start with Master in Column A and then run the get_areas($C2) function in all cells that DO start with master in Column A. If that is not feasible, then I would like to run the get_areas function in all cells that are blank (meaning they didn't return anything from the original formula, but still have the formula in them) in VBA. I have tried modifying the original formula to read
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">ActiveSheet.Range("J2:J" & intLastRow).FormulaR1C1 =
"=IFERROR(IF(LEFT(RC[-9],6)=""master"", get_areas(RC[-7]),
IF(ISNUMBER(MATCH(RC[3],Sheet1!C10,0)),""N"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C11,0)),""D"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C12,0)),""R"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C13,0)),""G"",
IF(ISNUMBER(MATCH(RC[3],Sheet1!C14,0)),""F"","""")))))), """")"</code>but received errors about the get_areas function.

 

[Worf]

Is this what you want?

Sub Vgresia()
Dim lr%, sh As Worksheet, i%
Set sh = Sheets("sheet2")
For i = 2 To sh.Range("a" & Rows.Count).End(xlUp).Row
    Select Case sh.Cells(i, 1) Like "Master*"
        Case True
            sh.Cells(i, 10).Formula = "=get_areas(c" & i & ")"
        Case False
            sh.Cells(i, 10).Formula = "=IFERROR(IF(ISNUMBER(MATCH(RC[3],Sheet1!C10,0)), ""N""," & _
            " IF(ISNUMBER(MATCH(RC[3],Sheet1!C11,0)),""D"",IF(ISNUMBER(MATCH(RC[3],Sheet1!C12,0)),""R""," & _
            "IF(ISNUMBER(MATCH(RC[3],Sheet1!C13,0)),""G"",IF(ISNUMBER(MATCH(RC[3],Sheet1!C14,0)),""F"",""""))))), """")"
    End Select
Next
End Sub

Function get_areas$(ID$)
Dim rng As Range, cel As Range, areas$, coff%
coff = 10                                   ' column offset
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cel In rng
    If IsNumeric(Left(cel, 1)) And cel.Offset(, 2) = ID Then
        If InStr(1, areas, cel.Offset(, coff)) = 0 Then areas = cel.Offset(, coff) & ", " & areas
    End If
Next
get_areas = Trim(Left(areas, Len(areas) - 2))
End Function