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

[mrmcoupe]

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.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]Dollar[/TD]
[TD][/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD][/TD]
[TD]AUP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Plan[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Plan[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]3[/TD]
[TD]19[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have tried to tweak the two following codes, but I am SOOOOO close, but there is not cigar smoke Any suggestions?

Sub Test()


    Dim r2 As Range
  
    Set r2 = Range("C3:E8" & i)
    r2 = Evaluate(r2.Address & "*200")
    
    
End Sub

and also this one:

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!

 

[Osvaldo Palmeiro]

Hi. Try:

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'

 

[mrmcoupe]

Hi. Try:

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!

 

[Osvaldo Palmeiro]

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)

 

[mrmcoupe]

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

 

[mrmcoupe]

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

 

[Osvaldo Palmeiro]

Hello, Håvard.

1. if the last row with data of your target table is always (e.g.) the row #40 then use:

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:

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:

For Each r In Range("B2:B" & ActiveCell.Row)

 

[mrmcoupe]

Hello, Håvard.

1. if the last row with data of your target table is always (e.g.) the row #40 then use:

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:

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:

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