How Do I aggregate rows up when there value is Yes?

[Brian Connelly]

I have an excel sheet where there are a lot of columnst that have either Yes, No and Null Values. Going down the list there are companies and they may appear multiple times, however the columns may have different values. I want to loop through the repeat company's values and if they are yes, paste the value into the first appearance of the company's name. For example, Company A appears three times. I want to loop trought the 2nd and third time and if there is a value of yes, copy and paste it into the first time appearance in the same column. I paste a sample view below.


Before View

<table border="1">
<tr>
<td>Company</td>
<td>A</td>
<td>A</td>
<td>A</td>
<td>B</td>
</tr>
<tr>
<td>Value 1</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td>Value 2</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td>Value 3</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
</tr>
</table> 
 
 

After VBA Code View

<table border="1">
<tr>
<td>Company</td>
<td>A</td>
<td>B</td>
</tr>
<tr>
<td>Value 1</td>
<td>Yes</td>
<td>Yes</td>
</tr>
<tr>
<td>Value 2</td>
<td>Yes</td>
<td>Yes</td>
</tr>
<tr>
<td>Value 3</td>
<td>Yes</td>
<td>Yes</td>
</tr>
</table> 
 

[zmbd]

Brain,
Required reading before you post: How to ask good questions - Read before submitting question

I'm not too sure how much help you're going to get here as this is an MS-Access forum.

I can only guess that your two code blocks are your Excel worksheets? In order to even begin to help I (read any of the experts/mods/users) would have to translate these - they are not usable as is!

Next, What have you tried to do? How have you tried to solve this problem? What specific error are you running in to?

-z

[Brian Connelly]

Yes, I made a lot of head way. This post has been changed and added to Visual Basic. And I made a mistake, I saw VBA and read VBE which is why I posted here.