Multiplying a Cell base on Adjacent Column

[madhuchelliah]

Hello Folks,I have a very complex requirement. I need a macro to multiply based on some conditions. I have a two column one is filled with "Y" and "N" letters another one is filled with numbers. My requirement is to identify all the "Y" succeeded by "N" and multiply the value of adjacent cell to 'N" with values of cells adjacent to "Y". There could be lot of combinations of N decent by Y. See the example below.

Source Result

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]10
[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]15
[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Sorry I couldn't be of more assistance

 

[madhuchelliah]

Hello Fluff, I identified the issue. Some of the cells in the H column contains TEXT values. Is there any fix to solve this?. Some times i am text values in H column

 

[FormR]

Hi, here is another option that you can try..

Sub m()
Dim a, i As Long, m As Double
a = Range("G1:H" & Range("G" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(a)
  If IsNumeric(a(i, 2)) Then
    If UCase(a(i, 1)) = "N" Then m = a(i, 2)
    If UCase(a(i, 1)) = "Y" Then a(i, 2) = a(i, 2) * m
  End If
Next i
Range("G1:H1").Resize(UBound(a)).Value = a
End Sub