Enter User Defined Function into Column Using VBA

Question

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

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"",""""))))), """")"

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.

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

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

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"","""")))))), """")"

but received errors about the get_areas function.

@BigBen I get Run-time error '5': Invalid procedure call or argument, and when I debug it, it highlights this part of the function areas = Trim(Left(areas, Len(areas) - 2)). — Victoria G
– Victoria G, Commented Dec 20, 2018 at 18:26
@BigBen However, if I just enter the formula =get_areas(C2) into cell J2, it works so I am not sure what the problem is. — Victoria G
– Victoria G, Commented Dec 20, 2018 at 19:26
Either way, you need to check If Len(areas) > 2 before attempting to use Left(areas, Len(areas) - 2) — BigBen
– BigBen, Commented Dec 20, 2018 at 19:29

VBasic2008 · Accepted Answer · 2018-12-20 23:27:57Z

1

WorksheetFunction.Trim

All of this might have nothing to do with your case but might be useful in some similar cases. It just keeps ringing in my head and you know how it is when you can't keep your mouth shut.

I would have written the function like this:

Function get_areas(ID As String) As String

Dim rng As Range
Dim i As Long
Dim areas As String

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

With rng
    For i = 1 To .Cells.Count
        If IsNumeric(Left(.Cells(i, 1))) And .Cells(i, 1).Offset(0, 2) = ID Then
            If InStr(1, areas, .Cells(i, 1).Offset(0, 9)) = 0 Then
                If i > 1 Then
                    areas = areas & ", " & .Cells(i, 1).Offset(0, 9)
                  Else
                    areas = .Cells(i, 1).Offset(0, 9)
                End If
            End If
        End If
    Next
End With

get_areas = WorksheetFunction.Trim(areas)

End Function

which in all is not so important as the 'WorksheetFunction' part.

WorksheetFunction.Trim removes all spaces except single spaces between words, while VBA's Trim function only removes the left and right spaces.

The other most noticeable difference is the 'If i > 1' block.

edited Dec 20, 2018 at 23:27

answered Dec 20, 2018 at 23:20

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Victoria G Over a year ago

Appreciate the edits! When I tried using this code I received an "argument not optional" error and it highlighted "Left" after If IsNumeric.

VBasic2008 Over a year ago

I forgot the left's second parameter. I should be 'IsNumeric(Left(.Cells(i,1),1))'. Sorry.

Victoria G Over a year ago

Thanks! This code works, but all of them start with ", " or ", ," etc. instead of the letter itself which is probably what the Trim was used for.

VBasic2008 Over a year ago

Maybe the 'If Instr' should be '<> 0', I mean I don't know what the code does. You are obviously getting empty separated values.

Victoria G Over a year ago

Hm, I'm not sure. Do you know how I could turn this into a Sub instead of a function?

|

Collectives™ on Stack Overflow

Enter User Defined Function into Column Using VBA

1 Answer 1

WorksheetFunction.Trim

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

WorksheetFunction.Trim

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related