Multiple cell values based on 2 columns with specific number

[CsJHUN]

Hi Board,

Does anyone has advice about this problem.
I have this 2 sample table ("A1:D11" and "F1:G6")

	A​	B​	C​	D​	E​	F​	G​
1​	Grouping​	Date​	Data name​	Data value​		Group​	Multiplier​
2​	1​	1/1/2017​	Need to multiply​	6​		1​	2​
3​	1​	1/1/2017​	Not need to multiply​	10​		2​	6​
4​	1​	1/1/2017​	Not need to multiply​	4​		3​	9​
5​	2​	1/1/2017​	Not need to multiply​	10​		4​	2​
6​	2​	1/1/2017​	Need to multiply​	5​		5​	6​
7​	2​	1/1/2017​	Not need to multiply​	4​
8​	3​	1/2/2017​	Not need to multiply​	5​
9​	1​	1/2/2017​	Need to multiply​	2​
10​	2​	1/2/2017​	Not need to multiply​	2​
11​	5​	1/2/2017​	Not need to multiply​	1​

<tbody>
</tbody>

I would like to multiple each group "need to multiply" values for each day with the group multiplier. (with macro if possible )
For example:
- D2 has to be 12 (6*2)
- D6 = 30 (5*6)
- D9 = 4 (2*2)

Thanks in advance!
John

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Feb15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("G2"), Range("G" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] Application
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Dn.Offset(, 3).Value = Dn.Offset(, 3).Value * _
        .Index(Rng2, .Match(Dn.Value, Rng2.Offset(, -1), 0))
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[My Aswer Is This]

Try this:
The value in column "C" must be exactly like this:
"Need to multiply"

Sub Grouping()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As Long
Dim anss As Long
Dim Lastrow As Long
Dim aa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        If Cells(i, "C").Value = "Need to multiply" Then
            ans = Cells(i, 1).Value
            aa = Cells(i, "D").Value
            anss = Range("F1").Offset(ans, 1).Value
            Cells(i, "D").Value = aa * anss
        End If
    Next

Application.ScreenUpdating = True
End Sub

 

[CsJHUN]

Thanks guys for replies both looks pretty promising. Didn't have time to check back soon enough. But managed to write (at and between meetings ) one macro which satisfy the leaders.
This is the result for the real (not example) sheets/tables

Sub vodor_szorzo()Dim vodor As Variant
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For Each vodor In Range("D2:D" & lr)    ' get the full range of 'data name'
    If vodor.Value = "vödör száma" Then   'vödör száma = need to modify
    paszta = vodor.Offset(0, -3).Value   'get the group
    szorzo_ertek = Range("Q" & Range("N1:N20").Find(paszta).Offset(-1, 0).Row + 1)  'find the group mutiplier
    vodor.Offset(0, 1).Value = vodor.Offset(0, 1).Value * szorzo_ertek   'write back the multiplied value
    End If
Next vodor


End Sub

Thank you very much again!

 

[My Aswer Is This]

Sounds like your saying you wrote your own script and do not need any more help from us.

Thanks guys for replies both looks pretty promising. Didn't have time to check back soon enough. But managed to write (at and between meetings ) one macro which satisfy the leaders.
This is the result for the real (not example) sheets/tables

Sub vodor_szorzo()Dim vodor As Variant
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For Each vodor In Range("D2:D" & lr)    ' get the full range of 'data name'
    If vodor.Value = "vödör száma" Then   'vödör száma = need to modify
    paszta = vodor.Offset(0, -3).Value   'get the group
    szorzo_ertek = Range("Q" & Range("N1:N20").Find(paszta).Offset(-1, 0).Row + 1)  'find the group mutiplier
    vodor.Offset(0, 1).Value = vodor.Offset(0, 1).Value * szorzo_ertek   'write back the multiplied value
    End If
Next vodor


End Sub

Thank you very much again!