使用VBA在列中输入用户定义的函数 [英] Enter User Defined Function into Column Using VBA

问题描述

我当前在工作簿的工作表2的J列中使用一个公式，该公式将从工作表1的5列中查找值并返回相应的文本.例如，如果工作表2的M列中的值与工作表1的J列中的任何值匹配，它将返回"N"，如果不匹配，它将在K列中查找，如果与之匹配则返回D，依此类推. .我在VBA中这样做，所以使用的公式是

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

此公式效果很好，并填写了相应的值.然后，我创建了一个用户定义的函数，该函数将查找J列中与C列中找到的ID号相关联的所有值，并用逗号分隔它们.当输入单元格时，此功能也很好用.

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

理想情况下，我想做的是在J列中所有不以Master开头的单元格中运行原始公式，然后在所有以master开头的单元格中运行get_areas($ C2)函数如果不可行，那么我想在VBA中的所有空白单元格中运行get_areas函数(这意味着它们没有从原始公式返回任何内容，但仍包含该公式).我尝试将原始公式修改为

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

，但是收到有关get_areas函数的错误.

but received errors about the get_areas function.

推荐答案

WorksheetFunction.Trim

所有这些可能与您的情况无关，但在某些类似情况下可能很有用.它只是一直在我的头上响，你知道当你不能闭上嘴时的感觉.

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.

我会这样写函数:

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

总的来说，它不如" WorksheetFunction "部分那么重要.

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

WorksheetFunction.Trim 删除单词之间除单个空格外的所有空格，而VBA的Trim函数仅删除左右空格.

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

另一个最明显的区别是'If i > 1'块.

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

这篇关于使用VBA在列中输入用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！