1行目から特定の文字列を検索し、ヒットした列の合計を求める。

前提・実現したいこと

1行目に入力された文字列から特定の文字列を検索し、ヒットした列の合計を求めるといった内容のコードを作成しています。

検索したい文字列は、「setting」と名前を付けたExcelブック上の「day」という名前を付けたセルに記載されています。

実行したい流れとしては
1．1行目から「day」を検索する。
2．ヒットした列の2行目以下に入力された値を合計する。
3．合計した値を変数に入力する。
というものになります。

条件について
1．検索したい行の長さは可変で、今後も増えていく予定です(入力されている内容が日付のため)。
2．計算したい列の長さも可変で、さらに、B列に特定の文字列が含まれているセルの値のみを合算します。
→ただし、最初にフィルターをかけるため、計算自体は目に見えているセルの値のみで行います。(こちらは恐らくSubtotal(109　～)のような記述になると思っています)

実力不足なうえ、一部を削っているためお見苦しいかと思いますが、下記に現在作成中のコードを記載しておりますので、何卒お力添えを頂けますよう、よろしくお願いいたします。

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

該当のソースコード

VBA
1
2'対象シートを開いた状態にしておく
3    taisyou.Activate
4    With ActiveSheet
5
6    If ActiveSheet.FilterMode = True Then
7'オートフィルタの絞り込みをクリア
8            ActiveSheet.ShowAllData
9    End If
10
11'結合されたセルをクリアして値を埋める
12  For Each rng In ActiveSheet.UsedRange
13    If rng.MergeCells Then
14      With rng.MergeArea
15        .UnMerge
16        .Value = .Resize(1, 1).Value
17      End With
18    End If
19  Next
20    End With
21    
22    With ActiveSheet
23        Rows(19).AutoFilter _
24            2, _
25            "<=" & SettingSht.Range("条件").Value
26
27'～～～～～ここから試行錯誤中～～～～～～
28
29'合計したい列の長さを特定する
30LastRow = Range("B1").End(xlDown).Row'
31
32'文字列を検索する行の最終行を特定し、この中から検索する。
33LastCol = Range("C1").End(xlToRight).Column
34
35'この下に行から文字列を特定して、ヒットした列の値を合算するコードを記載する予定
36Set year = Setting.Range("day").Value
37

試したこと

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

Excelバージョン：2016

行動規範の内容に同意します

回答1件

ベストアンサー

こんな感じでどうでしょうか。

VBA
1year = Setting.Range("day").Value
2
3Dim result, i
4For i = 3 To lastCol
5    If Cells(1, i).Value = year Then result = result + WorksheetFunction.Subtotal(109, Range(Cells(19, i), Cells(lastRow, i)))
6Next
7

Excel
1=SUM($C:$Z*($B:$B<=条件)*($C$1:$Z$1=day))
2

投稿2021/09/23 22:16

編集2021/09/23 22:22

jinoji

総合スコア4592

same_2614869

2021/09/24 04:56

お世話になっております。ご回答頂きありがとうございます。頂いた内容を試してみたのですが year = Setting.Range("day").Value この部分で実行時エラー 91 オブジェクト変数またはwithブロック変数が設定されていません。というエラーが発生してしまいます。ご回答頂いた内容に重ねてお尋ねするのは恐縮ではあるのですが、解消方法にお心当たりありましたら、ご教示いただければ幸いです。