エクセル マクロ 繰り返し処理について

マクロ勉強初めて1週間の初心者です。（エクセル自体あまり堪能ではないです）
正月の休みを利用して在庫管理表を作りたいのですが、困っています。ご教授お願いします。

いまやりたいことは
「発注リストシート」内の発注数を商品毎に合計して「在庫リストシート」の発注数セルに記入したい、です。

発注リストシート
発注番号　商品名　発注数・・・
　27-1　　　A　　　１０
　27-2　　　B　　　２０
　27-3　　　C　　　３０
　27-4　　　A　　　４０
　27-5　　　A　　　５０

在庫リストシート
商品番号　　商品名　発注数・・・
　１　　　　　A　　　100
　２　　　　　B　　　 20
　３　　　　　C　　　 30

としたいです。

自分なりにした事は、発注リストシートの商品名と発注数を変数(shouhin,hattyuusuu)にして、商品名が合致したら発注数を足してから在庫リストシートに記入しなさい、としました。


Sub 発注()

'発注数と商品名を変数にする

Dim hattyuusuu1 As Integer
hattyuusuu1 = [C3].Value
Dim shouhin1 As String
shouhin1 = [B3].Value

Dim hattyuusuu2 As Integer
hattyuusuu2 = [C4].Value
Dim shouhin2 As String
shouhin2 = [B4].Value 　　

Dim hattyuusuu3 As Integer
hattyuusuu3 = [C5].Value
Dim shouhin3 As String
shouhin3 = [B5].Value

Dim hattyuusuu4 As Integer
hattyuusuu4 = [C6].Value
Dim shouhin4 As String
shouhin4 = [B6].Value

Dim hattyuusuu5 As Integer
hattyuusuu5 = [C7].Value
Dim shouhin5 As String
shouhin5 = [B7].Value

'カレントシートを在庫リストに変更

Worksheets("在庫リスト").Activate

'発注数削除

Range("C3:C５").Select
Selection.ClearContents

'発27-1列の実行

If shouhin1 = "A" Then
[C3].Value = hattyuusuu1 + [C3]

ElseIf shouhin1 = "B" Then
[C4].Value = hattyuusuu1+ [C4]

ElseIf shouhin1 = "C" Then
[C5].Value = hattyuusuu1 + [C5]

Else
End If

'発27-2列の実行

If shouhin2 = "A" Then
[C3].Value = hattyuusuu2 + [C3]

ElseIf shouhin2 = "B" Then
[C4].Value = hattyuusuu2+ [C4]

ElseIf shouhin2 = "C" Then
[C5].Value = hattyuusuu2 + [C5]

Else
End If

'発27-3列の実行

If shouhin3 = "A" Then
[C3].Value = hattyuusuu3 + [C3]

ElseIf shouhin1 = "B" Then
[C4].Value = hattyuusuu3+ [C4]

ElseIf shouhin1 = "C" Then
[C5].Value = hattyuusuu3 + [C5]

Else
End If　　　　27-5列まで繰り返す...

End Sub

このように発注番号の数だけ繰り返すのは初心者とはいえ、あまりにもマクロ機能を無駄にしていると思えるので、「繰り返し処理」を利用できないかと考えているのですがどのようにしたらいいかがわかりません。
Do loop 文　あるいは　For Next 文というのを見つけましたがどのように使ったらいいのでしょうか。
よろしくお願いいたします。

No.1 ベストアンサー 回答者： tom04 回答日時： 2015/12/30 09:10

こんにちは！

今回の質問に関してはワークシート関数のSUMIF関数が一番簡単だと思います。
両Sheetとも1行目が項目行でデータはA2セル以降にあるとすると
「在庫リスト」SheetのC2セルに
=SUMIF(発注リスト!B:B,B2,発注リスト!C:C)
という数式を入れフィルハンドルでダブルクリック！
これで完了です。

どうしてもVBAでやりたい！というコトであればワークシート関数がそのまま使えますので

Sub Sample1()
Dim lastRow As Long, wS As Worksheet
With Worksheets("在庫リスト")
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
With Range(.Cells(2, "C"), .Cells(lastRow, "C"))
.Formula = "=SUMIF(発注リスト!B:B,B2,発注リスト!C:C)"
.Value = .Value
End With
End With
End Sub

でも大丈夫だと思います。

For～NextでループさせるにしてもSUMIF関数を使った方が簡単です。

Sub Sample2()
Dim i As Long, wS As Worksheet
Set wS = Worksheets("在庫リスト")
With Worksheets("発注リスト")
For i = 2 To wS.Cells(Rows.Count, "B").End(xlUp).Row
wS.Cells(i, "C") = WorksheetFunction.SumIf(.Range("B:B"), wS.Cells(i, "B"), .Range("C:C"))
Next i
End With
End Sub

こんな感じでしょうか。

※　非効率な方法（おススメしません）
両Sheetともそれぞれを最終行までループさせる方法。

人それぞれで色んなやり方があると思います。m(_ _)m

この回答へのお礼

ありがとうございます！出来ました！
第1段階突破です！

どうしてもVBA使いたいのではなく、数値が出ればいいんです。
でも関数でできると思わず、VBAの本を買ってしまいました。
エクセル関数の本を買ったほうが良さそうですね。
恥ずかしながら関数も加減剰余しかしてなくて...。

フィルハンドルでWクリックというのが、？　ですがSUMIFでなんとかできました。

とりあえず発注して、納品されて、出荷する、の結果在庫数が計算されました。
これで7割出来上がりです。

あとは注文書とか納品書とか、いろいろ連動できればなぁ...と夢見てます（笑）

お礼日時：2015/12/30 11:15

No.3 回答者： tom04 回答日時： 2015/12/30 12:21

No.1です。

＞フィルハンドルでWクリックというのが、？　ですが・・・

基本的な操作なので以下のコトをやってみてください。

「在庫リスト」のC2セルに数式が入っているとすると
C2セルの右下にマウスポインタを移動させます。
小さな■のマーク（フィルハンドル）になりますので、そこで下へドラッグしてください。
これがオートフィルの操作になります。

この操作をデータが入っている両隣りの隣接する列（どちらか最大行）の最終行までオートフィルの操作を一気にやる場合は
先ほどのフィルハンドルでダブルクリックします。

これで最終行まで数式が入ります。m(_ _)m

この回答へのお礼

ありがとうございます。
■マークを出してドラッグしてコピー、はやってました。
でも、それを「フィルハンドル」と呼ぶのは知りませんでしたし、Wクリックで一気にコピー！も初めて知りました。
ドラッグしながら下にコピーをたくさんするのは大変だったんです。

重ね重ねありがとうございました。

お礼日時：2015/12/31 00:00

No.2 回答者： hallo-2007 回答日時： 2015/12/30 11:57

在庫に関する考え方ですが、tom04さんが仰る様に

SumIf関数を使うのが一般的です。考え方は
入荷数の合計ー出荷数の合計＝現在の在庫
と考えてください。
それと、一応、VBAの勉強と云う事で

Sub Sheet3_ボタン1_Click()
With Sheets("発注リスト")
For i = 2 To .Range("B" & Rows.Count).End(xlUp).Row
shouhin = .Range("B" & i).Value
Columns("B:B").Find(What:=shouhin, LookAt:=xlWhole).Offset(0, 1).Value = Columns("B:B").Find(What:=shouhin, LookAt:=xlWhole).Offset(0, 1).Value + .Range("C" & i).Value
Next
End With
End Sub

を、在庫のシートの発注数を手動で一旦クリアしてから実行してみてください。

For i = 2 To .Range("B" & Rows.Count).End(xlUp).Row
で、発注リストの２行目から、B列のデータが入っている最後の行まで繰り返し。

Columns("B:B").Find(What:=shouhin, LookAt:=xlWhole).Offset(0, 1).Value
検索の機能を使って、商品名を検索して、一つ左へ移動したセルの値を
と云った感じで実行します。

この回答へのお礼

ありがとうございます。
今の自分の能力ではちょっと理解するのが困難ですが、ひとつひとつ噛み砕きながらやってみます。
For　Next文では出来ないかと思いましたが、できるんですね...。

がんばってみます。

お礼日時：2015/12/31 00:00