Multiply cell values with adjacent cell value in ranges if criteria is met

mrmcoupe

New Member
Joined
Jan 22, 2017
Messages
17
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.


ABCDEFG
DollarW1W2W3AUP
Plan25500
Actual141525
Plan1214100
Actual3192

<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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi. Try:
Code:
Sub Test3()
 Dim r As Range, str As String, i As Long
  
  If [A1] = "Dollar" Then
   str = "*"
  ElseIf [A1] = "Units" Then str = "/"
  End If
   
   For Each r In Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
    If r.Value = "Plan" Then
     For i = 1 To 3
      If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, 5).Address & str & r.Offset(, i).Address)
     Next i
    End If
   Next r
End Sub

Note - the code will place the result in columns 'H: J'
 
Upvote 0
Hi. Try:
Code:
Sub Test3()
 Dim r As Range, str As String, i As Long
  
  If [A1] = "Dollar" Then
   str = "*"
  ElseIf [A1] = "Units" Then str = "/"
  End If
   
   For Each r In Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
    If r.Value = "Plan" Then
     For i = 1 To 3
      If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, 5).Address & str & r.Offset(, i).Address)
     Next i
    End If
   Next r
End Sub

Note - the code will place the result in columns 'H: J'



Thanks! I worked almost 100% perfect! Just a small adjustment I believe!

I adjusted the offset as I wanted that the result to appear in the same rows as I enter the numbers. I am switching the numbers entered between a unit value and a dollar value, based on an Average Unit Price (col G). When multiplying, it works superb! But when dividing, the dividend is on the wrong side. So, instead of dividing a Dollar price with number of units, it divides number of units by dollar.

So, instead of (G/C:D) it should be (C:D/G).

Not sure if I explained it in an easy way, but if I could bother you to help me with this adjustment I would really appreciate it!

Thanks alot, again!
 
Upvote 0
I adjusted the offset as I wanted that the result to appear in the same rows ...
That's what the code already does. The result appears in the row whose data are being analyzed.

But when dividing, the dividend is on the wrong side. So, instead of dividing a Dollar price with number of units, it divides number of units by dollar.
So, instead of (G/C:D) it should be (C:D/G).
The code calculates as you wrote in the OP, like this:
"In the first case 500/25, next case 100/12 and 100/14."
But now it seems that what you want is: 25/500, 12/100 and 14/100. Isn't it? If so, change the line of the code as below.
change this line
If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, 5).Address & str & r.Offset(, i).Address)

to
If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, i).Address & str & r.Offset(, 5).Address)
 
Upvote 0
change this line
If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, 5).Address & str & r.Offset(, i).Address)

to
If r.Offset(, i) <> "" Then r.Offset(, i + 5).Value = Evaluate(r.Offset(, i).Address & str & r.Offset(, 5).Address)



Hi again Osvaldo!

Absolutely perfect! Works just as planned! Yes, you are correct regarding my request. It was me requesting the wrong calculation, and that is what I get then. ;) Sorry about that! My bad!

Thank you very much for your time and effort!

brg
Håvard
 
Upvote 0
Hi again Osvaldo!

Absolutely perfect! Works just as planned! Yes, you are correct regarding my request. It was me requesting the wrong calculation, and that is what I get then. ;) Sorry about that! My bad!

Thank you very much for your time and effort!

brg
Håvard


Hi!

I just realized an issue. I thought the ranges were set in the line: (For Each r In Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)) However, it seems arbitrary what row it stops in. In the example I sent, I had six rows (where half had "Plan" in them). However, the range is bigger; typically the table is B2:O40. But if I type Range ("B2:B40"...) it keeps on calculating in the next table that starts below. Is there a parameter that I miss here?

brg
Håvard

 
Upvote 0
Hello, Håvard.

1. if the last row with data of your target table is always (e.g.) the row #40 then use:
Code:
For Each r In Range("B2:B40")

2. if the last row isn't always the same # and if you have at least one empty cell between the target table and data below it and there isn't any empty cell in target table (looking only at column 'B' for this) then use:
Code:
For Each r In Range("B2:B" & Range("B2").End(4).Row)

3. If none of the above situations occur then you can select any cell in the row till which the code should process and then run the code, for this situation use:
Code:
For Each r In Range("B2:B" & ActiveCell.Row)
 
Upvote 0
Hello, Håvard.

1. if the last row with data of your target table is always (e.g.) the row #40 then use:
Code:
For Each r In Range("B2:B40")

2. if the last row isn't always the same # and if you have at least one empty cell between the target table and data below it and there isn't any empty cell in target table (looking only at column 'B' for this) then use:
Code:
For Each r In Range("B2:B" & Range("B2").End(4).Row)

3. If none of the above situations occur then you can select any cell in the row till which the code should process and then run the code, for this situation use:
Code:
For Each r In Range("B2:B" & ActiveCell.Row)


Hi!

I went for the first solution and it worked as planned. 100% perfect! You are awesome! :)

Thank you so much! You have really saved me for alot of work!

brg
Håvard
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top