teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

3

修正

2021/09/15 02:40

投稿

jinoji
jinoji

スコア4592

answer CHANGED
@@ -17,7 +17,8 @@
17
17
 
18
18
  Dim i As Long, fm As String
19
19
  For i = 2 To saki.Range("A" & saki.Rows.Count).End(xlUp).Row
20
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & "A" & i & "," & 合計範囲.Address(, , , True) & ")"
20
- fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
21
+ 'fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
21
22
  saki.Range("C" & i).Formula = fm
22
23
  Next
23
24
  ```

2

修正

2021/09/15 02:40

投稿

jinoji
jinoji

スコア4592

answer CHANGED
@@ -1,10 +1,4 @@
1
- こういうことですか?
2
- ```
3
- Range("C2").Formula = "=SUMIF(C3:C12, ""A005"", E3:E12)"
4
- ```
5
-
6
- ---
7
- 追記
1
+ 再修正
8
2
  ```VBA
9
3
  Dim 月次 As String
10
4
  月次 = "202108"
@@ -14,17 +8,16 @@
14
8
  Set moto = Worksheets("果物" & 月次)
15
9
  lastrow = moto.Range("B" & moto.Rows.Count).End(xlUp).Row
16
10
 
17
- Dim 範囲 As Range, 合計範囲 As Range, 検索条件 As String
11
+ Dim 範囲 As Range, 合計範囲 As Range
18
12
  Set 範囲 = moto.Range("C4:C" & lastrow)
19
13
  Set 合計範囲 = moto.Range("B4:B" & lastrow)
20
- 検索条件 = """りんご"""
14
+
21
-
22
15
  Dim saki As Worksheet
23
16
  Set saki = Worksheets("集計表")
24
17
 
25
- Dim fm As String
18
+ Dim i As Long, fm As String
19
+ For i = 2 To saki.Range("A" & saki.Rows.Count).End(xlUp).Row
26
- fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & 検索条件 & "," & 合計範囲.Address(, , , True) & ")"
20
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
27
- Debug.Print fm
28
-
29
- saki.Range("C2").Formula = fm
21
+ saki.Range("C" & i).Formula = fm
22
+ Next
30
23
  ```

1

追記

2021/09/15 02:34

投稿

jinoji
jinoji

スコア4592

answer CHANGED
@@ -1,4 +1,30 @@
1
1
  こういうことですか?
2
2
  ```
3
3
  Range("C2").Formula = "=SUMIF(C3:C12, ""A005"", E3:E12)"
4
+ ```
5
+
6
+ ---
7
+ <追記>
8
+ ```VBA
9
+ Dim 月次 As String
10
+ 月次 = "202108"
11
+
12
+ Dim moto As Worksheet, lastrow As Long
13
+
14
+ Set moto = Worksheets("果物" & 月次)
15
+ lastrow = moto.Range("B" & moto.Rows.Count).End(xlUp).Row
16
+
17
+ Dim 範囲 As Range, 合計範囲 As Range, 検索条件 As String
18
+ Set 範囲 = moto.Range("C4:C" & lastrow)
19
+ Set 合計範囲 = moto.Range("B4:B" & lastrow)
20
+ 検索条件 = """りんご"""
21
+
22
+ Dim saki As Worksheet
23
+ Set saki = Worksheets("集計表")
24
+
25
+ Dim fm As String
26
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & 検索条件 & "," & 合計範囲.Address(, , , True) & ")"
27
+ Debug.Print fm
28
+
29
+ saki.Range("C2").Formula = fm
4
30
  ```