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開発依頼受付はこちらから
今回は、「酒店舗別」「分類別」で集計を行います。
Withの使い方を理解して頂きたいです。
【課題】
シート「Sheet1」、「Sheet1_回答」を用意します。
シート「Sheet1」には、「酒店舗」「分類」「売上」を記載します。
シート「Sheet1_回答」には、シート「Sheet1」の酒店舗・分類別に売上を集計する。
※前提条件 シート「Sheet_回答」には、すべての酒店舗と分類が必ずあるとします。
【VBA】
Sub syukei_Clic()
Dim i As Long
Dim ixR As Long
Dim ixC As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet1_回答")
ws2.Range("A1").CurrentRegion.Offset(1, 1).ClearContents
With ws1
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
ixC = 2
Do Until ws2.Cells(1, ixC) = .Cells(i, 1)
ixC = ixC + 1
Loop
ixR = 2
Do Until ws2.Cells(ixR, 1) = .Cells(i, 2)
ixR = ixR + 1
Loop
ws2.Cells(ixR, ixC) = ws2.Cells(ixR, ixC) + .Cells(i, 3)
Next
End With
End Sub
簡単な説明
ws2.Range("A1").CurrentRegion.Offset(1, 1).ClearContents
・初期クリアーします
「With ws1」〜「End With」は「Sheet1」について処理を行う。
・「.Cells」、「.Rows.Count」については、「ws1.Cells」、「ws1.Rows.Count」を意味します。
・これが、「同じオブジェクト名を省略」ということになります。
「Do Until ws2.Cells(1, ixC) = .Cells(i, 1)」はループ処理を行う。
・「Sheet1」の酒店舗から「Sheet1_回答」と同じ酒店舗を探す。
「Do Until ws2.Cells(ixR, 1) = .Cells(i, 2)」はループ処理を行う。
・「Sheet1」の酒名から「Sheet1_回答」と同じ酒名を探す。
「ws2.Cells(ixR, ixC) = ws2.Cells(ixR, ixC) + .Cells(i, 3)」は、売上合計を算出する。
・「Sheet1」の各酒店舗、各酒名の売上を、「Sheet1_回答」に設定する。
あなたのお役に立てるなら、コピーして使用してください。
今後も勉強した内容を記載して行きますので、お役に立てるものがあれば使ってください。
VBA開発もしておりますので開発のご要望があればご連絡をください。
VBA開発依頼受付はこちらから
今回は、行の挿入、削除を行います。
【課題】
区分がD、No.が空欄の行を削除する。
区分がIの行の前に新規行を挿入する。
No.を上から昇順で振り直す
【VBA】
Sub InsDel()
Dim i As Long
Dim lp As Long
lp = Cells(Rows.Count, 1).End(xlUp).Row - 1
For i = lp To 2 Step -1
Select Case Cells(i, 2)
Case ""
Rows(i).Delete
End Select
Select Case Cells(i, 1)
Case "I"
Rows(i).Insert
Case "D"
Rows(i).Delete
End Select
Next
lp = Cells(Rows.Count, 1).End(xlUp).Row - 1
For i = 2 To lp
Cells(i, 2) = i - 1
Next
End Sub
簡単な説明
・「Cells(Rows.Count, 1).End(xlUp).Row」は、以前説明をしたのでこちらを御覧ください
A列の最後の「E」行までの行数を取得
・「Rows(i).Delete」は、行を削除する
A列の「D」が記入されているところと、B列の空欄の行を削除する
・「Rows(i).Insert」は、行を追加する
A列の「I」が記入されているところの前に１行追加する
・「Cells(i, 2) = i - 1」のForは、前処理の行の追加削除後にNo.を振り直すループを行う
・Selectについては、前回に説明したこの記事を御覧ください。
あなたのお役に立てるなら、コピーして使用してください。
今後も勉強した内容を記載して行きますので、お役に立てるものがあれば使ってください。
VBA開発もしておりますので開発のご要望があればご連絡をください。
VBA開発依頼受付はこちらから
