おやじのブログ

VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、
ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用してください。
ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いです。
しかも、劇的な速度向上になりますので、使わない手はない。
【課題】
シート「商品マスタ」より、商品名と単価を取得する。
シート「商品売上」のC列とD列に商品名と単価入れる。
シート「商品マスタ」に存在しない場合は、空欄のままにする。
シート「商品売上」の単価×数量を計算し金額（F列）に入れる。
シート「商品売上」のF12セルに合計金額を計算し入れる。

【VBA】

Sub wsf()
　　Dim i As Long
　　Dim ix As Long
　　Dim lngTotal As Long
　　Dim ws1 As Worksheet
　　Dim ws2 As Worksheet
　　Set ws1 = Worksheets("商品売上")
　　Set ws2 = Worksheets("商品マスタ")
　　lngTotal = 0
　　With ws1
　　　　For i = 2 To 11
　　　　　　ix = WorksheetFunction.CountIf(ws2.Columns(1), .Cells(i, 2))
　　　　　　If ix > 0 Then
　　　　　　　　ix = WorksheetFunction.Match(.Cells(i, 2), ws2.Columns(1), 0)
　　　　　　　　.Cells(i, 3) = ws2.Cells(ix, 2)
　　　　　　　　.Cells(i, 4) = ws2.Cells(ix, 3)
　　　　　　　　.Cells(i, 6) = .Cells(i, 4) * .Cells(i, 5)
　　　　　　　　lngTotal = lngTotal + .Cells(i, 6)
　　　　　　End If
　　　　Next
　　　　.Cells(12, 6) = lngTotal
　　End With
End Sub

簡単な説明
「With ws1」については、前回の記事を参照してください。
「WorksheetFunction.CountIf(ws2.Columns(1), .Cells(i, 2))」は、商品番号検索処理。
・シート「商品マスタ」と、シート「商品売上」の同じ商品番号の数を取得する。
「If ix > 0 Then」は、商品番号の数の比較をする。
・同じ商品番号があればIｆ文内の処理を行う。
「WorksheetFunction.Match(.Cells(i, 2), ws2.Columns(1), 0)」
・シート「商品マスタ」と、シート「商品売上」の完全一致の商品番号を検索を行う。
・最後の「０」が完全一致を表す。
・その他「-1」は、「検査値」以上の最小値。「1」は、「検査値」以下の最大値。
「.Cells(i, 3) = ws2.Cells(ix, 2)」は、シート「商品売上」に商品名を設定する。
「.Cells(i, 4) = ws2.Cells(ix, 3)」は、シート「商品売上」に単価を設定する。
「.Cells(i, 6) = .Cells(i, 4) * .Cells(i, 5)」は、シート「商品売上」の単価✗数量を設定する。
「lngTotal = lngTotal + .Cells(i, 6)」は、シート「商品売上」の合計に設定する値を算出。
「.Cells(12, 6) = lngTotal」は、シート「商品売上」の合計に設定する。

あなたのお役に立てるなら、コピーして使用してください。

今後も勉強した内容を記載して行きますので、お役に立てるものがあれば使ってください。

VBA開発もしておりますので開発のご要望があればご連絡をください。

VBA開発依頼受付はこちらから

• シェア
• 
• 
• 
•