我对VBA有些了解,我想使用VBA实现此目标。我将数据从表格转移到另一张,并带有某种特殊情况。
鉴于这种情况:在另一张纸中,我有这些范围
- A4:B10
- D2:E10
- G2:H10
我的数据是这样的
AXX | Contact no.
AXX | Address
AXX | Name
AXX | Summary
BXX | Address
BXX | Name
BXX | Contact no.
BXX | Details
CXX | Address
CXX | Name
CXX | Summary
DXX | Address
DXX | Name
DXX | Contact no.
DXX | Address
DXX | Name
我的标识符在第一列中(AXX,BXX ...)。
预期输出为:
Row no| Column A | Column B | Column D | Column E |
1 | | | | |
2 | | | BXX | Address |
3 | | | BXX | Name |
4 | AXX | Contact no. | BXX | Contact no. |
5 | AXX | Address | BXX | Details |
6 | AXX | Name | | |
7 | AXX | Summary | CXX | Address |
8 | | | CXX | Name |
9 | | | CXX | Summary |
10 | | | | |
如您所见,我的标识符是Axx,BXX ...如果它们相似,我将计算NO。行并将其与No进行比较。我一组范围的行。BXX没有放置在AXX旁边,因为其余行是3,但BXX需要4,因此它将传递到下一个范围。另外,将有一个空白细胞分开其他值,如Sees BXX和CXX。
目前,我只知道要计算使用循环的行。想为此寻求帮助。我的初始代码获取行计数
Dim aa, aaLastrow As Long
aaLastrow = ShtData.Range("A" & Rows.Count).End(xlUp).Row
For aa = 2 To aaLastrow
If ShtData.Cells(aa, 2).Value = ShtData.Cells(bb, 4).Value Then
Sheets("Sheet1").Cells(aa, 1).Value = ShtData.Cells(aa, 2).Value
End If
Next aa
我知道我的代码不正确,我不确定这种方法是否在正确的轨道上。
确实喜欢这个
Sub test()
Dim Data As Worksheet, ToWs As Worksheet
Dim vData, vDB, vArray
Dim i As Integer, j As Long, n As Long
Dim rngDB(1 To 4) As Range
Set Data = Sheets(1)
Set ToWs = Sheets(2)
vData = Data.Range("a1").CurrentRegion
vArray = Array("A", "B", "C", "D")
With ToWs
Set rngDB(1) = .Range("a4:b10")
Set rngDB(2) = .Range("d2:e5")
Set rngDB(3) = .Range("d7:e10")
Set rngDB(4) = .Range("g2:h10")
End With
For i = 1 To 4
n = 0
rngDB(i).Clear
vDB = rngDB(i)
For j = 1 To UBound(vData)
If vData(j, 1) Like vArray(i - 1) & "*" Then
n = n + 1
vDB(n, 1) = vData(j, 1)
vDB(n, 2) = vData(j, 2)
End If
Next j
rngDB(i) = vDB
Next i
End Sub
=COUNTIF($A:$A, "AXX")
将为您提供您似乎想要的计数。您可以完善范围,并且可以将引用插入到单元格,而不是硬" AXX"。如果您想使用VBA,则可以将该函数称为Application.Countif(Range, CountWhat)
,其中"范围"是您在VBA中定义的范围,而" Count Whath"是一个变体。