UDF连接值 [英] UDF to concatenate values

问题描述

我正在使用VBA for excel来构建一个用户定义的函数。这将连接在该行中具有斧头标记的商店列表。

I am trying to build a user defined function using VBA for excel. That would concatenate a list of stores which has a x mark in that row.

   Store1 Store2 Store3    Concatenate
      x             x      Store1,Store3  
      x      x             tore1,Store2
      x                    Store1

我设法写了这个vba代码，但是我不知道这是最好的方法。当我在1000线以上的线路上，这是相当缓慢的。也许可以优化它？指出第一家商店开始的位置（不是名称，而是x标记，最后一个存储1 ）的最后一个商店

I managed to write this vba code, but I am not sure this is the best approach. As I was tesing in on 1000 and more lines, it was quite slow. Maybe it is possible to optimise it?

列。 listofstores1 是商店名称的行。

Function listofstores(firstStore As Range, lastStore1 As Range, listofstores1 As Range)
    Application.Volatile

    Dim offsetvalue As Integer

    offsetvalue = -(lastStore1.Row - listofstores1.Row)

    lastStore = lastStore1.Column
    Set initial = firstStore

    For i = 1 To lastStore
    If initial = "X" Or initial = "x" Then Store = initial.Offset(offsetvalue, 0)
    c = 1
    Set initial = initial.Offset(0, c)
    listofstores = listofstores & " " & Store
    Store = ""


    Next i
    End Function

推荐答案

短而复杂。

1. 评估返回一个匹配数组（存储号码vx）


2. 过滤器删除不匹配（V）


3. 加入以使最终匹配数组中的字符串

1. uses Evaluate to return an array of matches (Store numbers v x)
2. Filter removes the non-matches ("V")
3. Join to make the string from the final array of matches

UDF

Function Getx(Rng1 As Range, Rng2 As Range) As String
Getx = Join(Filter(Evaluate("=ÏF(" & Rng2.Address & "=""x""," & Rng1.Address & ",""V"")"), "V", False), ",")
End Function

这篇关于UDF连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！