指定した範囲のセル内の数値を検索したい(VBA)

質問

いつもありがとうございます。
また皆様のお知恵を拝借したいと思い質問をさせて頂きました。

現在VBAを勉強中なのですが、以下の場合、コードはもっと簡単になるでしょうか？

【やりたい事】
プログラムを実行すると、シート２に数値が返されます。
　※数値が返される範囲は「A1～A100」だとします。

シート2の「A1～A100」には「１～４」の数値が返され、それぞれの数値が何個あるかを検索。
検索した結果を、「シート１」の指定したセルに表示する。

【記述したコード】

dim x as integer

x = 0

For x = 1 To 101

If Sheets("シート2").Cells(x, 1) = "1" Then

1count = 1count + 1

End If

If Sheets("シート2").Cells(x, 1) = "2" Then

2count = 2count + 1

End If

If Sheets("シート2").Cells(x, 1) = "3" Then

3count = 3count + 1

End If

If Sheets("シート2").Cells(x, 1) = "4" Then

4count = 4count + 1

End If

Next x

上記のコードで「"x"count」に数値を加算していき、最終的に以下のように各数値をシート１に表示させています。

Sheets("シート1").Range("A1") = 1count
Sheets("シート1").Range("A2") = 2count
Sheets("シート1").Range("A3") = 3count
Sheets("シート1").Range("A4") = 4count

結果的にはうまくカウントされて、結果も正しく表示されるのですが、
列をまとめて検索してやる方法などがあれば、もっと短く分かりやすく
おさまるのではと思い、質問をさせて頂きました。

こうやるともっと簡単にできるよなどがあれば、教えて頂けないでしょうか。
Excelの関数などを使用しても構いません。

以上、よろしくお願いします。

回答No.4

tom04 · ベストアンサー

こんばんは！
横からお邪魔します。

すでに解決済みのようですが・・・
ごく簡単に

Sub Sample1()
Dim i As Long
For i = 1 To 4
Worksheets("シート1").Cells(i, "A") = WorksheetFunction.CountIf(Worksheets("シート2").Range("A:A"), i)
Next i
End Sub

といった具合ではどうでしょうか？m（_ _）m

お礼

結論から言うと、コレを採用しました…ｗ

たしかに、これが一番シンプルで理解しやすかったです。
質問の回答としては、この回答をベストアンサーとさせて頂きます。

※VBAを始めてまだ3日とか4日なので、他の方にも色々と教えて頂いており、とても助かっています！
　tom04さんも他の方も今後ともよろしくお願いいたします＞＜

回答No.9

MarcoRossiItaly

No.7 です。連続ですみませんが、補足を。今回の場合 COUNTIF で個数を拾うだけなので、No.4 さんのように range(a:a) で十分であり、....End(xlUp) と記述する意味は、実はありません。説明用とご理解ください。

回答No.8

kmetu

> For Each c In Sheets("シート2").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
>
> この部分ですが、少しでいいので詳しく教えて頂けないでしょうか；；
>
> 特に以下の部分を教えて頂きたいです；；
> Range("A" & Rows.Count)

For Each～nextは
こういうパターンがあると単純に覚えてください。指定したセル範囲のセルを順次変数cに代入してくれます。

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

A1からA列の最終データの行までを範囲指定しています。これもこういうパターンですね。

Range("A" & Rows.Count)

質問者さんが解釈されたとおり変数が利用できるということです。
質問にあった　Cells(x, 1)　でも変数が利用できますが、ぱっと見たときに何列なのか分かりにくいのが難点だと感じていますので、個人的には行が変化する場合にはRange("A" & 変数)を使うことが多いです。列で変数を使う場合にはCells(１, 変数)を使います。
ちなみに、Rows.Countは行の最大数を表す変数でエクセルのバージョンによって最大行数が違うので使っています。

.End(xlUp).Rowは
指定した列(今回はA列)の最終行から検索して最初に見つかったデータが入っているセルの行をさします。

お礼

何度もありがとうございます。
そして、詳しく教えて頂き大変感謝です…。

>質問にあった　Cells(x, 1)　でも変数が利用できますが、ぱっと見たときに何列なのか分かりにくい>のが難点だと感じていますので、個人的には行が変化する場合にはRange("A" & 変数)を使うことが多>いです。列で変数を使う場合にはCells(１, 変数)を使います。
>ちなみに、Rows.Countは行の最大数を表す変数でエクセルのバージョンによって最大行数が違うので使>っています。

なるほど…。
変数が利用できるのですね！
知りませんでした＾＾；

しかも、行と列で変数を使用できるのは大変便利ですね！
本当に分かりやすく教えて頂き大変助かります；；

これからもよろしくお願いします＞＜

回答No.7

MarcoRossiItaly

If ステートメントで条件を 4 つ書くなら、If ... ElseIf ... ElseIf (... ElseIf) (... Else) ... End If という構文で書くのが普通です。

「= "1"」などの「""」は、セルの書式などが何であっても、不要です。基本的に数値には常に、「""」を付けないと覚えましょう。

Cells(Rows.Count, "a").End(xlUp).Row というコードについて。どこかのセルにカーソルがあるときにショートカットキー Ctrl+↓を何回か押していると、シートのいちばん下の行にカーソルが移動します。Excel 2007 以降のバージョンなら、1,048,576 行目であるはずです。Excel 2003 以前なら、65,536 行目です。Rows はシートの全ての行であり、Rows.Count というのは、この 1,048,576 あるいは 65,536 のことです。

そのいちばん下の行の A 列にカーソルを置いてください。その位置からショートカットキー Ctrl+↑を 1 回押したときにカーソルが止まった位置のセルが Cells(Rows.Count, "a").End(xlUp) に当たります。Cells(Rows.Count, "a").End(xlUp).Row は、その止まった位置の行番号です。

セルに記入するものが値であるにしろ数式であるにしろ、基本的にワークシート関数を使ってできる処理なら、ループとかカウンターよりも関数のほうが効率的です。次のコードは、コード上で COUNTIF 関数を使い、計算結果の値をセルに記入する一例です。ああ、No.4 さんのご回答とほぼ一緒ですね。

Dim i As Long
For i = 1 To 4
　　Worksheets("シート1").Cells(i, "a").Value = _
　　WorksheetFunction.CountIf(Worksheets("シート2").Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row), i)
Next i

お礼

ご回答ありがとうございます！

>「= "1"」などの「""」は、セルの書式などが何であっても、不要です。基本的に数値には常に、
>「""」を付けないと覚えましょう。

なるほど・・・。
たしかに、””をつけるのがクセになってそうな気がします…。
そして、気が付かないうちにエラーとなる・・・と。
プログラムは奥が深いですね；；

>セルに記入するものが値であるにしろ数式であるにしろ、基本的にワークシート関数を使ってできる処
>理なら、ループとかカウンターよりも関数のほうが効率的です。

たしかに関数のほうが分かりやすいですし、効率的だと思います。
私も教えられて関数を使用したのですが、なぜか処理が遅くなってしまったのですよね…。
原因が分かりませんｗ

回答No.6

tsubuyuki

横からしゃしゃり出てすいません。

ひとまず・・軽く説明から。
　　Rows　⇒　「行」を指定するときに使います。
　　　　　　　Rows(1)　で１行目、Rows("1:4")　とすると１から４行目を意味します。
　　　　　　　括弧内を省略すると「全行」を意味します。
　　.Count　⇒　「要素の数」を返します。
コレを組み合わせて、「Rows.Count」としてやると、「全行の数」の意味になり、
「２００３以前なら６５５３６、２００７以降なら１０４８５７６」を返します。
さらに「Range("A" & Rows.Count)」と組み合わせることによって、
２００７以降の場合「Ａ１０４８５７６セル」を意味する呪文に変身します。
さらにさらに「.End(xlUp)」を付けると、
「そこから上に向かって最初に値が入力されているセル」を意味し、
最後に「.Row」を付けると、そのセルの行番号を返してくれます。

つまり、
　　Range("A" & Rows.Count).End(xlUp).Row
コレで「Ａ列の最終行」を調べること呪文を唱えることができるのです。

なので、
> 「シート2!A1:A100」の部分ですが、「A100」の部分が変動するので、
> できれば変数で指定できるようにしたいのです。
としたいなら、
　　　　Dim MRow As Long
　　　　MRow = Range("A" & Rows.Count).End(xlUp).Row
としてやることで、変数「MRow」にＡ列の最終行を入れることが出来ます。

さて、考え方の一つとしてこんなやり方もありかと。
つまり・・１～４の出現数をシート１のＡ１～Ａ４セルに入れたい、
と言うことで、もう直接書いてやります。

Sub sample()
Dim i As Long

　　　　Sheets("シート1").Range("A1:A4").ClearContents
　　　　For i = 1 To Sheets("シート2").Range("A" & Rows.Count).End(xlUp).Row
　　　　　　　　Sheets("シート1").Range("A" & Sheets("シート2").Range("A" & i)).Value = Sheets("シート1").Range("A" & Sheets("シート2").Range("A" & i)).Value + 1
　　　　Next
End Sub

あまりにも横に長くて見辛いので、慣れてきたらＷｉｔｈを使って

Sub sample()
Dim i As Long

With Sheets("シート1")
　　　　.Range("A1:A4").ClearContents
　　　　For i = 1 To Sheets("シート2").Range("A" & Rows.Count).End(xlUp).Row
　　　　　　　　With .Range("A" & Sheets("シート2").Range("A" & i))
　　　　　　　　　　　　.Value = .Value + 1
　　　　　　　　End With
　　　　Next
End With
End Sub

こんな感じで同じ意味のコードをちょっとだけスッキリ書けます。

ちなみにコード中の「＆」は、文字列を結合する演算子です。
エクセルのワークシート上での使い方と一緒ですよ。

お礼

細かくご回答頂きありがとうございます！

Rows関数…これって結構便利なのでは・・・。
>Rows　⇒　「行」を指定するときに使います。
>　　　　　　　Rows(1)　で１行目、Rows("1:4")　とすると１から４行目を意味します。
>　　　　　　　括弧内を省略すると「全行」を意味します。

知ってる人から見ると当たり前でしょうが、私のような初心者には目からうろこです。
さっそく一部のコードをRows関数を用いて修正したいと思います。

>つまり、
>　　Range("A" & Rows.Count).End(xlUp).Row
>コレで「Ａ列の最終行」を調べること呪文を唱えることができるのです。

私も、tsubuyukiさんのように、たくさんの呪文が唱えられるように頑張りますｗ
ありがとうございました！

またお願いします＞＜

回答No.5

noname#184106

こんばんわ。
私なら、ここは関数です。
Sheet1のA1：=COUNTIF(Sheet2!$A$1:$A$100,1)
Sheet1のA2：=COUNTIF(Sheet2!$A$1:$A$100,2)
Sheet1のA3：=COUNTIF(Sheet2!$A$1:$A$100,3)
Sheet1のA4：=COUNTIF(Sheet2!$A$1:$A$100,4)

もしマクロにこだわるなら、こんな感じでしょうか。
Sub Count4()
Sheets("Sheet1").Range("A1") = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A100"), 1)
Sheets("Sheet1").Range("A2") = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A100"), 2)
Sheets("Sheet1").Range("A3") = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A100"), 3)
Sheets("Sheet1").Range("A4") = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A100"), 4)
End Sub

うまくいくといいですね。

お礼

ご回答いただきありがとうございます！

>私なら、ここは関数です。
>Sheet1のA1：=COUNTIF(Sheet2!$A$1:$A$100,1)
>Sheet1のA2：=COUNTIF(Sheet2!$A$1:$A$100,2)
>Sheet1のA3：=COUNTIF(Sheet2!$A$1:$A$100,3)
>Sheet1のA4：=COUNTIF(Sheet2!$A$1:$A$100,4)

関数も試したのですが、処理が著しく遅くなってしまい、泣く泣く断念しました；；
Formula関数で数式を入れても結果は同じでした。

関数のほうが簡単でラクにできるのでイイんですけどね＞＜

回答No.3

kmetu

> あと、「シート2!A1:A100」の部分ですが、「A100」の部分が変動するので、
> できれば変数で指定できるようにしたいのです。

関数は

=COUNTIF(シート2!A:A,"1")

にすればいいです。

VBAの方は

For Each c In Sheets("シート2").Range("A1:A100")

のところを

For Each c In Sheets("シート2").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

に変更してください

補足

わざわざ補足を頂きありがとうございます。
初心者なので色々教えて頂き大変助かっております。

For Each c In Sheets("シート2").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

この部分ですが、少しでいいので詳しく教えて頂けないでしょうか；；

特に以下の部分を教えて頂きたいです；；
Range("A" & Rows.Count)

自分なりの解釈では、セルを「A"変数"」みたいに書けるという事でしょうか？
もしそうなら、「＆」は便利ですね！

回答No.2

kmetu

VBAならこんな感じでも

Sub test()

Dim MyCount(4) As Integer, i As Integer
Dim c As Range

For Each c In Sheets("シート2").Range("A1:A100")

Select Case c.Value
Case "1"
MyCount(1) = MyCount(1) + 1
Case "2"
MyCount(2) = MyCount(2) + 1
Case "3"
MyCount(3) = MyCount(3) + 1
Case "4"
MyCount(4) = MyCount(4) + 1
Case Else
End Select

Next

With Sheets("シート1")
For i = 1 To 4
.Range("A" & i) = MyCount(i)
Next i
End With

End Sub

補足

たしかに、Selectでやったほうが分かりやすいですね！！

他の部分でも同じ形式で併用できそうなので、これを採用したいと思います。

ありがとうございました！！！

回答No.1

kmetu

A1に

=COUNTIF(シート2!A1:A100,"1")

A2に

=COUNTIF(シート2!A1:A100,"2")

A3に

=COUNTIF(シート2!A1:A100,"3")

A4に

=COUNTIF(シート2!A1:A100,"4")

でいかがでしょう。

補足

ご回答頂きありがとうございます！
たしかに、関数のCOUNTIFでうまくいきそうですね！

ご教授頂いた内容でうまく表示されましたが、かなり処理速度が低下してしまいました；；
VBAのコードで式などを埋め込んだほうが早いのでしょうか…。
そもそも、そんな事ができるか不明ですが；；

あと、「シート2!A1:A100」の部分ですが、「A100」の部分が変動するので、
できれば変数で指定できるようにしたいのです。

COUNTIF関数でセルを変数にする事は可能なのでしょうか？