连接数量未知的细胞 [英] Concatenate an unknown number of cells

问题描述

这是我最初提出的问题的后续问题，它帮助我解决了脚本的第一部分：自动填充未知数量的单元格。

This is a follow on question from my original question which has helped me solve the first part of my script: Autofill unknown amount of cells.

脚本的第二部分是连接未知数量的单元格。
使用第一个问题的逻辑，我尝试将脚本更改为此 myconcatenate = ActiveCell.FormulaArray = = Concatenate（ A5，mylastcell_4）
但这会导致语法错误。

The second part of my script is to concatenate an unknown number of cells. Using the logic from the first question I have tried to alter my script for to this myconcatenate = ActiveCell.FormulaArray = "=Concatenate("A5", mylastcell_4)" But it is resulting in a syntax error.

最初，我得到的这段脚本本身可以很好地运行但添加到我的脚本底部时不起作用

Originally I was given this piece of script which worked fine on its own but doesn't work when added to the bottom of my script

  Do
    myconcatenate = myconcatenate & ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
  Loop Until ActiveCell.Address = mylastcell_5

  Range("A5").Select
  ActiveCell.Value = myconcatenate

这会给出运行时错误'1004'应用程序定义或对象-定义的错误，并将转到第5行的最后一个单元格（即使我目前在第1行中只有5个），并且在通过所有单元格时不执行任何操作。有时它有时会执行脚本的第一位（将''添加到单元格值中），而不执行其他操作。

This gives a run time error '1004' Application-defined or object-defined error and will go to the very last cell on row 5 (even though I only have 5 currently occupied in Row 1) and does nothing while it passes through all the cells. Sometimes it will do the first bit of the script (adds '', to the cell values) other times it doesn't.

这是我完整的上下文脚本： 完整脚本的屏幕截图

Here is my full script for context: Screenshot of full script

最终，我试图从abcdedf列表中将 a， b， c， d， e， f列表放入一个单元格中。

Ultimately I am trying to get from a list of abcdedf to 'a','b','c','d','e','f', into one cell.

最终目标图片：

第5单元格D给出我追求的最终答案是E5是 mylastcell_5

这是我在VBA上的第一天，我整天都在使用Google搜索尝试回答问题的方法，但这对我没有任何帮助。

This is my first day on VBA, and I have spent the whole day googling bits to try and answer questions but this hasn't resulted in anything for me.

更新：我已经声明了mylastcell_5，而我没有以前做过。我现在在脚本中没有出现任何错误，它只是在A5处闲逛，而不是转到页面的尽头，但这实际上不是串联的。

UPDATE: I've declared mylastcell_5 which I hadn't done previously. I'm now getting no error in the script and it is just hanging out at A5 rather than going to the very end of the page, but it's not actually concatenating.

因此，我认为我需要以某种方式更改循环，以使其写入第5行，但从第4行读取...但我不知道该怎么做。

Option Explicit
Sub concat()
'
' concat Macro
' 'text','text'
'
' Keyboard Shortcut: Ctrl+Shift+C
'
With ThisWorkbook.Worksheets("Sheet1")
Dim mylastcell_1 As String, mylastcell_2 As String, mylastcell_3 As String, mylastcell_4 As String, mylastcell_5 As String, myconcatenate As String, myconcatenate1 As String
    Range("A1").Select
    Selection.End(xlToRight).Select
    mylastcell_1 = ActiveCell.Address

    Range(mylastcell_1).Select
    ActiveCell.Offset(1, 0).Select
    mylastcell_2 = ActiveCell.Address

    Range(mylastcell_2).Select
    ActiveCell.Offset(1, 0).Select
    mylastcell_3 = ActiveCell.Address

    Range(mylastcell_3).Select
    ActiveCell.Offset(1, 0).Select
    mylastcell_4 = ActiveCell.Address

    Range(mylastcell_4).Select
    ActiveCell.Offset(1, 0).Select
    mylastcell_5 = ActiveCell.Address

    myconcatenate = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select


    Range("A2").Select
    Selection.End(xlToRight).Select
    Range("A2").Select
    Range("A2", mylastcell_2).Select
    Selection.FormulaArray = "''"
    Range("A3").Select
    Range("A3", mylastcell_3).Select
    Selection.FormulaArray = "'',"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C,R[-3]C,R[-1]C)"
    Range("A4").Select
   .Range(.Cells(4, "A"), .Cells(1, .Columns.Count).End(xlToLeft).Offset(3, 0)).Formula = "=concatenate(a2, a1, a3)"
    Range("A5").Select

Do
        myconcatenate = myconcatenate & ActiveCell.Value
        ActiveCell.Offset(0, 1).Select

    Loop Until ActiveCell.Address = mylastcell_5

    Range("A5").Select
    ActiveCell.Value = myconcatenate




End With
End Sub

推荐答案

我不知道您是否始终打算将元素连接到单引号中并以逗号分隔的字符串，但是如果这样做有两种方法（例如，我发现将其快速插入SQL语句很有用）。一种连接垂直字符串（单列），另一种连接水平字符串（单行）。这将是一个标准的工作表函数，您可以在其中输入 = hjoin（a1：h1）或 = vjoin（a1：a7）

I don't know if you always plan to concatenate elements into a single-quote enclosed comma-delimited string, but if you do here are a couple ways to do that (I've found it useful for quickly inserting things into SQL statements, for example). One joins vertical strings (single column) and one joins horizontal strings (single row). This would be a standard worksheet function where you would enter something like =hjoin(a1:h1) or =vjoin(a1:a7)

Function hJoin(inRng As Range) As String
Dim c As Range
Dim outStr As String
Dim rng As Range

Set rng = Range(inRng.Address)


For Each c In rng.Columns
    If c.Address = rng.Cells(rng.Cells.Count).Address Then
            outStr = outStr & " '" & c.Value & "'"
        Else
            outStr = outStr & " '" & c.Value & "',"
        End If
    Next c

hJoin = outStr

End Function

Function vJoin(inRng As Range) As String
Dim c As Range
Dim outStr As String
Dim rng As Range

Set rng = Range(inRng.Address)


For Each c In rng.Rows
    If c.Address = rng.Cells(rng.Cells.Count).Address Then
         outStr = outStr & " '" & c.Value & "'"
    Else
         outStr = outStr & " '" & c.Value & "',"
    End If
Next c

vJoin = outStr

End Function

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