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]

How about

Sub MultiplyCols()

   Dim Rng As Range
   Dim Cl As Range
   
   With Range("G1", Range("G" & Rows.Count).End(xlUp))
      .Replace "y", "#N/A", xlWhole, , False, , False, False
      For Each Rng In .SpecialCells(xlConstants, xlErrors).Areas
         For Each Cl In Rng.Offset(, 1)
            Cl.Value = Cl.Value * Rng.Offset(-1, 1).Resize(1, 1).Value
         Next Cl
      Next Rng
      .Replace "#N/A", "Y", xlWhole, , False, , False, False
   End With

End Sub

 

[madhuchelliah]

Thanks Fluff. Its working great !!!!!!!!!!!!!

 

[madhuchelliah]

Hi Fluff one small issue. If any of the cell in H column is empty the code is not running. It stopped after changing the G column cell values to #N/A. Any fix for this?

 

[Fluff]

I cannot replicate what you getting, what error message do you get?

 

[madhuchelliah]

I didn't got any error. After the code runs the the cells having the Y letter not replacing back to Y, it stops in #N/A. The multiplication also not done. For better understanding delete any of the numerical value in column H adjacent to CELL contains Y. Then run the code you will get the error what i am trying to explain.

 

[Fluff]

When I do that all that happens, is that the col H cells next to a Y read as 0 (zero) & the #N/A values become Y again.

 

[madhuchelliah]

It is not reading as Zero and the #N/A values not become Y again. But its not happening always, occasionally happening. When i fill the blank cells with some value the code is running properly.

 

[Fluff]

Unfortunately, I cannot replicate the problem you are getting. Which makes it difficult to figure out what is happening.
Do you have any worksheet or workbook events?

 

[madhuchelliah]

Its OK Fluff i will fill the blank cells with zero before running the code. Thanks for your valuable time.