VBA プログラミング WorksheetFunctionの使い方

目安時間:約 5分

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」は、商品番号の数の比較をする。
・同じ商品番号があればIf文内の処理を行う。
「WorksheetFunction.Match(.Cells(i, 2), ws2.Columns(1), 0)」
・シート「商品マスタ」と、シート「商品売上」の完全一致の商品番号を検索を行う。
・最後の「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開発依頼受付はこちらから

VBA プログラミング Withで同じオブジェクト名を省略

目安時間:約 4分

今回は、「酒店舗別」「分類別」で集計を行います。
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開発依頼受付はこちらから

VBAで「行の挿入・削除」やってみる

目安時間:約 3分

今回は、行の挿入、削除を行います。
【課題】
区分が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」が記入されているところの前に1行追加する
・「Cells(i, 2) = i - 1」のForは、前処理の行の追加削除後にNo.を振り直すループを行う
・Selectについては、前回に説明したこの記事を御覧ください。

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

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

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

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

1 2 3 12
プロフィール

50歳、派遣社員。いつもいつも派遣切りにビクビクしながら生きています。50歳という大台を迎えると派遣先がほとんどなく、次の派遣先は無いに等しい。でも、家庭を支えなければならない。だから私は、複数の仕事が出来るように在宅ワークが出来るプログラミング、ブログを選択。50歳からフリーランスを目指してプログラミング、ブログを始めました。

私と一緒に始めませんか?

お問い合わせ
プログラミング依頼

 

最近の投稿
アーカイブ

ページの先頭へ