Excel的VBA宏连接 [英] VBA Macro Concatenation for Excel
本文介绍了Excel的VBA宏连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
X
然后我要列标题和列A值连接。 例如
我在Stack Overflow中遇到了很多问题,我没有发现任何有用的东西。
感谢您的帮助!
解决方案
请尝试此代码。
Sub FindValues(ByVal WhereToFind As Range,ByVal WhereToPaste As Range)
'在哪里找到应该有标题和值
Dim col As Integer'循环通过列
Dim row As Integer'循环通过行
Dim a()As Variant
Dim b()As Variant
Dim i As Integer
a()= WhereToFind
对于行= 2到UBound(a,1)
对于col = 2对于UBound(a,2)
如果(row,col)=x然后
i = i + 1
ReDim保存b(1到i)
b(i)= a(1,col)& ; =& a(row,1)
End If
Next
Next
WhereToPaste.Resize(UBound(b))。Value = Application.Transpose(b())
End Sub
应该像
Sub caller()
FindValues ThisWorkbook.Sheets(Sheet1)。Range(A1:E4),ThisWorkbook.Sheets(Sheet1)。Range )
End Sub
输出就像
I have a requirement like I have some values in column A and I have multiple values in column B,C,D. If my column contains value X
then I want to column header and column A value to be concatenate.
For example
I have gone through lots of question on Stack Overflow and I didn't found anything helpful.
Thanks for your help!
解决方案
please try this code.
Sub FindValues(ByVal WhereToFind As Range, ByVal WhereToPaste As Range)
'where to find should have the header and values
Dim col As Integer 'loop through columns
Dim row As Integer 'loop through rows
Dim a() As Variant
Dim b() As Variant
Dim i As Integer
a() = WhereToFind
For row = 2 To UBound(a, 1)
For col = 2 To UBound(a, 2)
If a(row, col) = "x" Then
i = i + 1
ReDim Preserve b(1 To i)
b(i) = a(1, col) & "=" & a(row, 1)
End If
Next
Next
WhereToPaste.Resize(UBound(b)).Value = Application.Transpose(b())
End Sub
that should be called like
Sub caller()
FindValues ThisWorkbook.Sheets("Sheet1").Range("A1:E4"), ThisWorkbook.Sheets("Sheet1").Range("F1")
End Sub
the output is like
这篇关于Excel的VBA宏连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文