| |
▼初心者 さん：
あと、個数カウントにも For〜Nextや For Each〜Nextのループから、
ワークシート関数まで いろんな方法があるので、
２、３例をば。
Sub Sample1()
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim R As Long, LastR As Long
Dim I As Long, I2 As Long, I3 As Long
Set SH1 = Worksheets(1)
Set SH2 = Worksheets(2)
With SH2
LastR = .Range("A65536").End(xlUp).Row
For R = 1 To LastR
Select Case .Cells(R, 1).Value
Case "AAA": I = I + 1
Case "BBB": I2 = I2 + 1
Case "CCC": I3 = I3 + 1
End Select
Next
End With
SH1.Range("C5").Value = I & "件"
SH1.Range("C6").Value = I2 & "件"
SH1.Range("C7").Value = I3 & "件"
End Sub
Sub Sample2()
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim c As Range, I As Long
Dim Count(1 To 3) As Long
Set SH1 = Worksheets(1)
Set SH2 = Worksheets(2)
With SH2
For Each c In .Range("A1", .Range("A65536").End(xlUp))
Select Case c.Value
Case "AAA": Count(1) = Count(1) + 1
Case "BBB": Count(2) = Count(2) + 1
Case "CCC": Count(3) = Count(3) + 1
End Select
Next
End With
With SH1.Range("C5")
For I = 1 To 3
.Item(I, 1).Value = Count(I) & "件"
Next
End With
End Sub
Sub Sample3() 'COuntIfを使う
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim Rng As Range
Dim I As Long
Set SH1 = Worksheets(1)
Set SH2 = Worksheets(2)
With SH2
Set Rng = .Range("A1", .Cells(65536, 1).End(xlUp))
End With
With SH1.Range("C5")
.Value = Application.CountIf(Rng, "AAA") & "件"
.Offset(1).Value = Application.CountIf(Rng, "BBB") & "件"
.Offset(2).Value = Application.CountIf(Rng, "CCC") & "件"
End With
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
