Hi guys!
I have a table ("B:G") where the column headers are weeks ("C:E"). And in column G I have different dynamic values. I am trying to multiply the values in range C:E if the criteria in column B is met. So, in short this is what I am trying to do:
If value A1 = "dollar" and
If column B="Plan" then all of the cells in that range(C:E) should multiply with the value in cell G. In the first case 25*500, next case 12*100 and 14*100.
If value A1 = "units" and
If column B="Plan" then all of the cells in that range(C:E) should divide with the value in cell G. In the first case 500/25, next case 100/12 and 100/14.
I will use this for a button where I can switch between units and dollars.
<tbody>
</tbody>
I have tried to tweak the two following codes, but I am SOOOOO close, but there is not cigar smoke Any suggestions?
and also this one:
Thanks!
I have a table ("B:G") where the column headers are weeks ("C:E"). And in column G I have different dynamic values. I am trying to multiply the values in range C:E if the criteria in column B is met. So, in short this is what I am trying to do:
If value A1 = "dollar" and
If column B="Plan" then all of the cells in that range(C:E) should multiply with the value in cell G. In the first case 25*500, next case 12*100 and 14*100.
If value A1 = "units" and
If column B="Plan" then all of the cells in that range(C:E) should divide with the value in cell G. In the first case 500/25, next case 100/12 and 100/14.
I will use this for a button where I can switch between units and dollars.
A | B | C | D | E | F | G |
Dollar | W1 | W2 | W3 | AUP | ||
Plan | 25 | 500 | ||||
Actual | 14 | 15 | 25 | |||
Plan | 12 | 14 | 100 | |||
Actual | 3 | 19 | 2 | |||
<tbody>
</tbody>
I have tried to tweak the two following codes, but I am SOOOOO close, but there is not cigar smoke Any suggestions?
Code:
Sub Test()
Dim r2 As Range
Set r2 = Range("C3:E8" & i)
r2 = Evaluate(r2.Address & "*200")
End Sub
and also this one:
Code:
Sub Test2()
Dim i As Long, r1 As Range, r2 As Range, r3 As Range
For i = 2 To 8
Set r1 = Range("B" & i)
Set r2 = Range("C" & i & ":E" & i)
Set r3 = Range("G" & i)
If r1.Value = "Plan" Then r2 = r3 * r2
Next i
End Sub
Thanks!