質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.36%

VBA 数式の挿入について

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 386

score 2

前提・実現したいこと

CSVファイルからデータをExcelに取り込み、条件に合うデータの個数を集計するマクロを作成したいと考えています。

発生している問題・エラーメッセージ

現在はデータの個数の集計結果を表示する(図の911行目)ようなコードにしていますが、
図の赤枠内を変更したら変更が反映されるように、countifの数式が入るようにしたいのです。
調べてみると「Formula」を使うといいことは分かったのですが、コードの記述につまずいて
おります。宜しくお願い致します。


イメージ説明

該当のソースコード

現在のコードを以下に記します。

Sub 考課取込()
Dim fname As Variant
Dim wb As Workbook
Dim sh As Worksheet
Dim r As Long
Dim rng As Range
fname = Application.GetOpenFilename("CSVファイル(*.csv),*.csv", MultiSelect:=False)
If VarType(fname) = vbBoolean Then Exit Sub
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set wb = Workbooks.Open(fname)
Set sh = wb.Worksheets(1)
For r = 1 To sh.Cells(Rows.Count, 1).End(xlUp).Row
.Cells(r + 1, 1).Resize(, 2).Value = sh.Cells(r, 6).Resize(, 2).Value
.Cells(r + 1, 3).Resize(, 3).Value = sh.Cells(r, 30).Resize(, 3).Value
Next r
wb.Close
.Cells(r + 2, 2).HorizontalAlignment = xlCenter
.Cells(r + 2, 2).Interior.Color = RGB(255, 242, 204)
.Cells(r + 2, 2).Value = "A"
Set rng = .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
.Cells(r + 2, 3).HorizontalAlignment = xlCenter
.Cells(r + 2, 3).Interior.Color = RGB(255, 242, 204)
.Cells(r + 2, 3).Value = WorksheetFunction.CountIf(rng, "A")
Set rng = .Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row)
.Cells(r + 2, 4).HorizontalAlignment = xlCenter
.Cells(r + 2, 4).Interior.Color = RGB(255, 242, 204)
.Cells(r + 2, 4).Value = WorksheetFunction.CountIf(rng, "A")
Set rng = .Range("E2:E" & .Cells(Rows.Count, 5).End(xlUp).Row)
.Cells(r + 2, 5).HorizontalAlignment = xlCenter
.Cells(r + 2, 5).Interior.Color = RGB(255, 242, 204)
.Cells(r + 2, 5).Value = WorksheetFunction.CountIf(rng, "A")

(以下省略)

試したこと

ここに問題に対して試したことを記載してください。

補足情報(FW/ツールのバージョンなど)

ここにより詳細な情報を記載してください。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

+1

特定のセル範囲が変更されたときに処理を実行するのように、C2:E7の範囲が編加工されたときに、再集計するようにWorksheet_change()イベントに集計コードを埋め込めばできるでしょう。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/01/23 23:56

    教えていただきありがとうございます。こんな方法もあるのですね。勉強になります。
    一点お聞きしたいのは、その都度行数が変わる場合でも、この方法は有効なのでしょうか。今回はたまたま5人ですが、10人だったり100人だったりもするので、どのように範囲を特定したらよいでしょうか。
    列数が変わることはありません。

    キャンセル

  • 2021/01/24 18:16

    質問については、始めから反応範囲を広くとっておくか、
    end(xldown)やend(xltoright)で調べたデータ範囲を反映して、activecellの座標が所定の範囲にあるかを判定してから、ふさわしい範囲で WorksheetFunction.CountIf()関数を働かせると実現可能です。

    キャンセル

  • 2021/01/25 09:11

    ご回答いただきありがとうございます。返信が遅くなってしまい申し訳ありません。
    なるほど...いろんなやり方があるのですね!ネットでもいろいろ検索してはいるのですが、素人なもので、文字の羅列を見るだけでげんなりしています...。この方法でも試してみたいと思います!
    ありがとうございました。

    キャンセル

checkベストアンサー

0

現状のコードが結果が正しく表示されているのなら、

.Cells(r + 2, 3).Value = WorksheetFunction.CountIf(rng, "A")


上記のコードを下記に変更するだけです。

.Cells(r + 2, 3).Formula= "=COUNTIF(" & rng.Address & ", ""A"")"


以降の WorksheetFunction.CountIf の部分も上記のように変更してください。


下記のように一気に書式と数式を設定することもできます。

'省略

    With Worksheets("Sheet1")

'省略

        With .Cells(r + 2, 2).Resize(3, 4)
            .HorizontalAlignment = xlCenter
            .Rows(1).Interior.Color = RGB(255, 242, 204)
            .Rows(2).Interior.Color = RGB(204, 255, 242)
            .Rows(3).Interior.Color = RGB(242, 204, 255)
            .Cells(1, 1).Value = "A"
            .Cells(2, 1).Value = "B"
            .Cells(3, 1).Value = "C"
            .Offset(, 1).Resize(, 3).Formula = "=COUNTIF(C$3:C$" & r & ",$B" & r + 2 & ")"
        End With
    End With

矩形セル範囲に数式を設定する場合、左上のセルの数式を Formula に設定すると、右と左にオートフィルしたのと同様に数式が変換されて設定されます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/01/25 08:26

    ご回答いただきありがとうございます。返信が遅くなってしまい申し訳ありません。
    コードを変更する方法、書式と数式を一気に設定する方法、両方試してみました。どちらも自分の思い通りのものができ、思わず「お~」と声を上げてしまいました!
    VBAは素人なのでだらだら書いてしまいがちですが、こうして一気に設定できるとすっきりしていいですね。大変助かりました。ありがとうございました!!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.36%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る