teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップVBAに関する質問ExcelVBA(SUMIFで数式を残したい)

編集履歴

回答編集履歴

3

修正

2021/09/15 02:40

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -36,7 +36,9 @@
36
36
 
37
37
  For i = 2 To saki.Range("A" & saki.Rows.Count).End(xlUp).Row
38
38
 
39
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & "A" & i & "," & 合計範囲.Address(, , , True) & ")"
40
+
39
- fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
41
+ 'fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
40
42
 
41
43
  saki.Range("C" & i).Formula = fm
42
44
 

2

修正

2021/09/15 02:40

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -1,16 +1,4 @@
1
- こういうことですか？
2
-
3
- ```
4
-
5
- Range("C2").Formula = "=SUMIF(C3:C12, ""A005"", E3:E12)"
6
-
7
- ```
8
-
9
-
10
-
11
- ---
12
-
13
- ＜追記＞
1
+ ＜再修正＞
14
2
 
15
3
  ```VBA
16
4
 
@@ -30,15 +18,13 @@
30
18
 
31
19
 
32
20
 
33
- Dim 範囲 As Range, 合計範囲 As Range, 検索条件 As String
21
+ Dim 範囲 As Range, 合計範囲 As Range
34
22
 
35
23
  Set 範囲 = moto.Range("C4:C" & lastrow)
36
24
 
37
25
  Set 合計範囲 = moto.Range("B4:B" & lastrow)
38
26
 
39
- 検索条件 = """りんご"""
27
+
40
-
41
-
42
28
 
43
29
  Dim saki As Worksheet
44
30
 
@@ -46,14 +32,14 @@
46
32
 
47
33
 
48
34
 
49
- Dim fm As String
35
+ Dim i As Long, fm As String
50
36
 
51
- fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & 検索条件 & "," & 合計範囲.Address(, , , True) & ")"
37
+ For i = 2 To saki.Range("A" & saki.Rows.Count).End(xlUp).Row
52
38
 
53
- Debug.Print fm
39
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & ",""" & saki.Range("A" & i).Value & """," & 合計範囲.Address(, , , True) & ")"
54
40
 
55
-
41
+ saki.Range("C" & i).Formula = fm
56
42
 
57
- saki.Range("C2").Formula = fm
43
+ Next
58
44
 
59
45
  ```

1

追記

2021/09/15 02:34

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -5,3 +5,55 @@
5
5
  Range("C2").Formula = "=SUMIF(C3:C12, ""A005"", E3:E12)"
6
6
 
7
7
  ```
8
+
9
+
10
+
11
+ ---
12
+
13
+ ＜追記＞
14
+
15
+ ```VBA
16
+
17
+ Dim 月次 As String
18
+
19
+ 月次 = "202108"
20
+
21
+
22
+
23
+ Dim moto As Worksheet, lastrow As Long
24
+
25
+
26
+
27
+ Set moto = Worksheets("果物" & 月次)
28
+
29
+ lastrow = moto.Range("B" & moto.Rows.Count).End(xlUp).Row
30
+
31
+
32
+
33
+ Dim 範囲 As Range, 合計範囲 As Range, 検索条件 As String
34
+
35
+ Set 範囲 = moto.Range("C4:C" & lastrow)
36
+
37
+ Set 合計範囲 = moto.Range("B4:B" & lastrow)
38
+
39
+ 検索条件 = """りんご"""
40
+
41
+
42
+
43
+ Dim saki As Worksheet
44
+
45
+ Set saki = Worksheets("集計表")
46
+
47
+
48
+
49
+ Dim fm As String
50
+
51
+ fm = "=SUMIF(" & 範囲.Address(, , , True) & "," & 検索条件 & "," & 合計範囲.Address(, , , True) & ")"
52
+
53
+ Debug.Print fm
54
+
55
+
56
+
57
+ saki.Range("C2").Formula = fm
58
+
59
+ ```