Sir_BlahBlah
New Member
- Joined
- Apr 17, 2020
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
So this is a simplified version of a problem I am having in a much larger VBA macro.
Essentially I need to perform the following worksheet calculations in VBA:
=STDEV.S(IF(A1:A100 = "a",B1:B100))
=STDEV.S(IF((A1:A100 = "a")+(A1:A100 = "c")+(A1:A100 = "e")+(A1:A100 = "h")+(A1:A100 = "i"),B1:B100))
=STDEV.S(IF((A1:A100 = "a")*(B1:B100>12),B1:B100))
=STDEV.S(IF((A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12),B1:B100))
The first issue I had is that there isn't a worksheet function for an IF statement so I reworked the calculations to the following:
=STDEV.S(FILTER(B1:B100, A1:A100 = "a"))
=STDEV.S(FILTER(B1:B100,(A1:A100="a")+(A1:A100="c")+(A1:A100="e")+(A1:A100="h")+(A1:A100="i")))
=STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12)))
=STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12)))
I was able to code the first calculation pretty simply:
But I am really struggling with the additional conditions. the EVALUATE statement seems to be what is really holding me up here. I have tried the following and much more, but everything gives me a 'Type Mismatch' error.
I feel like I am just getting hung up in the syntax of the EVALUATE statement, but I also know that I am not the most efficient at coding so maybe there is a simpler way too.
Essentially I need to perform the following worksheet calculations in VBA:
=STDEV.S(IF(A1:A100 = "a",B1:B100))
=STDEV.S(IF((A1:A100 = "a")+(A1:A100 = "c")+(A1:A100 = "e")+(A1:A100 = "h")+(A1:A100 = "i"),B1:B100))
=STDEV.S(IF((A1:A100 = "a")*(B1:B100>12),B1:B100))
=STDEV.S(IF((A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12),B1:B100))
The first issue I had is that there isn't a worksheet function for an IF statement so I reworked the calculations to the following:
=STDEV.S(FILTER(B1:B100, A1:A100 = "a"))
=STDEV.S(FILTER(B1:B100,(A1:A100="a")+(A1:A100="c")+(A1:A100="e")+(A1:A100="h")+(A1:A100="i")))
=STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12)))
=STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12)))
I was able to code the first calculation pretty simply:
VBA Code:
Sub TestStDev()
Dim sdNum1 As Double
sdNum1 = Application.WorksheetFunction.StDev_S(Application.WorksheetFunction.Filter(ActiveWorkbook.Sheets("Sheet1").Range("B1:B100"), Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""a""")))
ActiveWorkbook.Sheets("Sheet1").Range("E1").Value = sdNum1
End Sub
But I am really struggling with the additional conditions. the EVALUATE statement seems to be what is really holding me up here. I have tried the following and much more, but everything gives me a 'Type Mismatch' error.
VBA Code:
x1 = Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""a""") + Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""c""") + Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""e""") + Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""h""") + Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""i""")
x2 = Evaluate(ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""a""") & "+" & (ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""c""") & "+" & (ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""e""") & "+" & (ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""h""") & "+" & (ActiveWorkbook.Sheets("Sheet1").Range("A1:A100").Address & "=""i""")
I feel like I am just getting hung up in the syntax of the EVALUATE statement, but I also know that I am not the most efficient at coding so maybe there is a simpler way too.
test.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | a | 14.08 | 2.363841 | 2.363841 | 2.363841 | ||
2 | b | 11.33 | 2.601406 | 2.601406 | |||
3 | c | 13.40 | 2.063844 | 2.063844 | |||
4 | d | 16.62 | 2.249341 | 2.249341 | |||
5 | e | 17.41 | |||||
6 | f | 18.79 | |||||
7 | g | 19.17 | |||||
8 | h | 10.24 | |||||
9 | i | 10.90 | |||||
10 | j | 10.44 | |||||
11 | a | 15.88 | |||||
12 | b | 17.47 | |||||
13 | c | 15.81 | |||||
14 | d | 12.57 | |||||
15 | e | 12.73 | |||||
16 | f | 14.30 | |||||
17 | g | 15.12 | |||||
18 | h | 12.11 | |||||
19 | i | 10.97 | |||||
20 | j | 16.36 | |||||
21 | a | 18.40 | |||||
22 | b | 10.55 | |||||
23 | c | 11.75 | |||||
24 | d | 17.81 | |||||
25 | e | 15.84 | |||||
26 | f | 17.46 | |||||
27 | g | 13.94 | |||||
28 | h | 11.94 | |||||
29 | i | 11.80 | |||||
30 | j | 13.93 | |||||
31 | a | 12.05 | |||||
32 | b | 19.24 | |||||
33 | c | 11.41 | |||||
34 | d | 16.75 | |||||
35 | e | 12.37 | |||||
36 | f | 14.89 | |||||
37 | g | 19.71 | |||||
38 | h | 14.49 | |||||
39 | i | 17.13 | |||||
40 | j | 11.44 | |||||
41 | a | 14.29 | |||||
42 | b | 15.72 | |||||
43 | c | 11.51 | |||||
44 | d | 19.81 | |||||
45 | e | 11.58 | |||||
46 | f | 13.65 | |||||
47 | g | 19.03 | |||||
48 | h | 11.78 | |||||
49 | i | 15.55 | |||||
50 | j | 15.92 | |||||
51 | a | 16.48 | |||||
52 | b | 17.39 | |||||
53 | c | 16.79 | |||||
54 | d | 18.32 | |||||
55 | e | 10.46 | |||||
56 | f | 19.05 | |||||
57 | g | 12.66 | |||||
58 | h | 13.35 | |||||
59 | i | 13.38 | |||||
60 | j | 14.82 | |||||
61 | a | 18.55 | |||||
62 | b | 14.53 | |||||
63 | c | 12.79 | |||||
64 | d | 17.78 | |||||
65 | e | 18.03 | |||||
66 | f | 12.49 | |||||
67 | g | 12.46 | |||||
68 | h | 12.33 | |||||
69 | i | 18.39 | |||||
70 | j | 18.59 | |||||
71 | a | 15.71 | |||||
72 | b | 17.89 | |||||
73 | c | 11.67 | |||||
74 | d | 19.05 | |||||
75 | e | 16.06 | |||||
76 | f | 17.21 | |||||
77 | g | 19.01 | |||||
78 | h | 12.72 | |||||
79 | i | 11.69 | |||||
80 | j | 17.60 | |||||
81 | a | 15.40 | |||||
82 | b | 11.18 | |||||
83 | c | 16.23 | |||||
84 | d | 12.66 | |||||
85 | e | 17.63 | |||||
86 | f | 11.86 | |||||
87 | g | 17.17 | |||||
88 | h | 14.22 | |||||
89 | i | 19.75 | |||||
90 | j | 14.85 | |||||
91 | a | 11.41 | |||||
92 | b | 10.51 | |||||
93 | c | 13.54 | |||||
94 | d | 16.83 | |||||
95 | e | 14.41 | |||||
96 | f | 17.05 | |||||
97 | g | 12.62 | |||||
98 | h | 12.15 | |||||
99 | i | 19.02 | |||||
100 | j | 19.64 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1 | C1 | =STDEV.S(IF(A1:A100 = "a",B1:B100)) |
D1 | D1 | =STDEV.S(FILTER(B1:B100, A1:A100 = "a")) |
C2 | C2 | =STDEV.S(IF((A1:A100 = "a")+(A1:A100 = "c")+(A1:A100 = "e")+(A1:A100 = "h")+(A1:A100 = "i"),B1:B100)) |
D2 | D2 | =STDEV.S(FILTER(B1:B100,(A1:A100="a")+(A1:A100="c")+(A1:A100="e")+(A1:A100="h")+(A1:A100="i"))) |
C3 | C3 | =STDEV.S(IF((A1:A100 = "a")*(B1:B100>12),B1:B100)) |
D3 | D3 | =STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12))) |
C4 | C4 | =STDEV.S(IF((A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12),B1:B100)) |
D4 | D4 | =STDEV.S(FILTER(B1:B100, (A1:A100 = "a")*(B1:B100>12)+(A1:A100 = "c")*(B1:B100>12)+(A1:A100 = "e")*(B1:B100>12)+(A1:A100 = "h")*(B1:B100>12)+(A1:A100 = "i")*(B1:B100>12))) |