Find string, do calculation

W

wibni

New Member
Joined
Jun 15, 2011
Messages
33
Hi,

I'm very new to Excel VBA and got a challenge which I'm not sure how to start with.
The below spreadsheet is generated by our financial system
The top 3 rows are code from the financial reporter.
Starting at row 6 is the output.

I need to do calculations with these results which can't be done by in a formula I'm afraid, so has to be VBA.

What I'm trying to achieve is to distribute the values under SiteBalance (column P) accross rows that belong in the same category.
For example the $20,914 in column P needs to be distributed accross all rows which have the same Country, AccSegment and Site.
So for that example it $20,914 needs to be divided by 6 because there are 6 accounts which match the criteria above (Country = 94, AccSegment = 5111 and Site = GGM).

Could someone please advise how to achieve this in VBA? i would actually prefer an Excel formula but don't think that can be done.

Any help much appreciated.


<table bordercolordark="#FFFFFF" border="1" bordercolor="#C0C0C0" cellpadding="2" cellspacing="0"> <tbody><tr> <td style="white-space: nowrap">E</td> <td style="white-space: nowrap">J</td> <td style="white-space: nowrap">K</td> <td style="white-space: nowrap">L</td> <td style="white-space: nowrap">M</td> <td style="white-space: nowrap">N</td> <td style="white-space: nowrap">O</td> <td style="white-space: nowrap">P</td> <td style="white-space: nowrap">Q</td> </tr> <tr> <td style="white-space: nowrap">AcctID</td> <td style="white-space: nowrap">Country</td> <td style="white-space: nowrap">AccSegment</td> <td style="white-space: nowrap">ManCo</td> <td style="white-space: nowrap">RigSite</td> <td style="white-space: nowrap">Site</td> <td style="white-space: nowrap">Current Period</td> <td style="white-space: nowrap">SiteBalance</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">#NAME?</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">\</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">AcctID</td> <td style="white-space: nowrap">Country</td> <td style="white-space: nowrap">AccSegment</td> <td style="white-space: nowrap">ManCo</td> <td style="white-space: nowrap">RigSite</td> <td style="white-space: nowrap">Site</td> <td style="white-space: nowrap">Current Period</td> <td style="white-space: nowrap">SiteBalance</td> <td style="white-space: nowrap">Reallocation</td> </tr> <tr> <td style="white-space: nowrap">10-4110-008</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-208,985</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4110-028</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-174,000</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4110-038</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">038</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-242,657</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4110-055</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-210,230</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4110-066</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-221,175</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4110-073</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4110</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">073</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-240,455</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4420-008</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4420</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-3,747</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4420-028</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4420</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-16,975</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4420-055</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4420</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-3,747</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4420-066</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4420</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-3,747</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-008</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-17,138</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-028</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-5,302</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-038</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">038</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-247</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-055</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-15,740</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-066</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-18,363</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-4430-073</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">4430</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">073</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">-5,272</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">94-5111-008-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">14,456</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-028-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">8,553</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-038-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">038</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">12,551</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-055-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">14,004</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-066-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">17,588</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-073-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">073</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">20,624</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">3485.66</td> </tr> <tr> <td style="white-space: nowrap">94-5111-GGM-910</td> <td style="white-space: nowrap">94</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">20,914</td> <td style="white-space: nowrap">20,914</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-5111-008</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">9,565</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-028</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">4,018</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-038</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">038</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">639</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-055</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">9,324</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-066</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">6,789</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-073</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">073</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">4,540</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">4990.16</td> </tr> <tr> <td style="white-space: nowrap">10-5111-GGM</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5111</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">29,941</td> <td style="white-space: nowrap">29,941</td> <td style="white-space: nowrap">
</td> </tr> <tr> <td style="white-space: nowrap">10-5113-008</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">008</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">1,052</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-028</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">028</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">442</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-038</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">038</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">268</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-055</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">055</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">1,052</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-066</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">066</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">747</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-073</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">073</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">579</td> <td style="white-space: nowrap">0</td> <td style="white-space: nowrap">548.83</td> </tr> <tr> <td style="white-space: nowrap">10-5113-GGM</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">5113</td> <td style="white-space: nowrap">10</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">GGM</td> <td style="white-space: nowrap">3,293</td> <td style="white-space: nowrap">3,293</td> <td style="white-space: nowrap">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Sorry Peter,
I had them grouped manually in order to be able to better explain what is required.
It should be ok to group them based on CSS.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
wibni said:
Sorry Peter,
I had them grouped manually in order to be able to better explain what is required.
It should be ok to group them based on CSS.
Click to expand...
I'm struggling a bit with this but try this new approach ..

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Spread_values()<br>    <SPAN style="color:#00007F">Dim</SPAN> aRows, AMatchCols<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, RwsReqd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Amt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Site <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, CSS1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, CSS2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 11 <SPAN style="color:#007F00">'<-- First Row of actual data</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> NumSites <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4 <SPAN style="color:#007F00">'<--No. of possible sites</SPAN><br>    AMatchCols = Array("J", "K", "N") <SPAN style="color:#007F00">'<--Cols that must match (CSS)</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    x = <SPAN style="color:#00007F">UBound</SPAN>(AMatchCols)<br>    aRows = Range("E1:F" & NumSites).Value<br>    LR = Range("P" & Rows.Count).End(xlUp).Row<br>    Range("E" & FR & ":P" & LR).Sort Key1:=Range("J" & FR), Order1:=xlAscending, _<br>        Key2:=Range("K" & FR), Order2:=xlAscending, Key3:=Range("N" & FR), _<br>        Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _<br>        <SPAN style="color:#00007F">Or</SPAN>ientation:=xlTop<SPAN style="color:#00007F">To</SPAN>Bottom, DataOption1:=xlSortNormal, _<br>        DataOption2:=xlSortNormal<br>    r = LR<br>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Cells(r, "P") <> 0 <SPAN style="color:#00007F">Then</SPAN><br>            Site = Cells(r, AMatchCols(3)).Value<br>            i = 0<br>            <SPAN style="color:#00007F">Do</SPAN><br>                i = i + 1<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> aRows(i, 1) = Site Or i = NumSites<br>            <SPAN style="color:#00007F">If</SPAN> aRows(i, 1) = Site <SPAN style="color:#00007F">Then</SPAN><br>                RwsReqd = aRows(i, 2)<br>            <SPAN style="color:#00007F">Else</SPAN><br>                MsgBox "Site not found in table"<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            Amt = Cells(r, "P").Value / RwsReqd<br>            CSS1 = ""<br>            <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> x<br>                CSS1 = CSS1 & "|" & Cells(r, AMatchCols(j)).Value<br>            <SPAN style="color:#00007F">Next</SPAN> j<br>            <SPAN style="color:#00007F">For</SPAN> k = 1 <SPAN style="color:#00007F">To</SPAN> RwsReqd<br><SPAN style="color:#007F00">'                r = r - 1</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> r >= FR - 1 <SPAN style="color:#00007F">Then</SPAN><br>                    z = r<br>                    CSS2 = ""<br>                    <SPAN style="color:#00007F">For</SPAN> j = 1 To x<br>                        CSS2 = CSS2 & "|" & Cells(r - 1, AMatchCols(j)).Value<br>                    <SPAN style="color:#00007F">Next</SPAN> j<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    z = FR<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> CSS2 = CSS1 <SPAN style="color:#00007F">Then</SPAN><br>                    Cells(r - 1, "Q").Value = Amt<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> Rows(z)<br>                        .Insert<br>                        Cells(z, "E").Resize(, 11).Value = _<br>                            Cells(z + 1, "E").Resize(, 11).Value<br>                        Cells(z, "Q") = Amt<br>                        r = r + 1<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                r = r - 1<br>            <SPAN style="color:#00007F">Next</SPAN> k<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        r = r - 1<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> r >= FR<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Thanks Peter.
I tested your code over the weekend and tried again to understand what you are doing. :laugh:
I've made 3 changes in your code. Could please let em know whether they are correct?
I think in you code CSS did not include column J.

Code: 
Do
        If Cells(r, "P") <> 0 Then
            Site = Cells(r, AMatchCols([COLOR=red]2[/COLOR])).Value
            i = 0
            Do
                i = i + 1
            Loop Until aRows(i, 1) = Site Or i = NumSites
            If aRows(i, 1) = Site Then
                RwsReqd = aRows(i, 2)
            Else
                MsgBox "Site not found in table"
                Exit Sub
            End If
            Amt = Cells(r, "P").Value / RwsReqd
            CSS1 = ""
            For j = [COLOR=red]0[/COLOR] To x
                CSS1 = CSS1 & "|" & Cells(r, AMatchCols(j)).Value
            Next j
            For k = 1 To RwsReqd
'                r = r - 1
                If r >= FR - 1 Then
                    z = r
                    CSS2 = ""
                    For j = [COLOR=red]0[/COLOR] To x
                        CSS2 = CSS2 & "|" & Cells(r - 1, AMatchCols(j)).Value
                    Next j
                Else
                    z = FR
                End If
 
Upvote 0
wibni said:
I've made 3 changes in your code. Could please let em know whether they are correct?
Click to expand...
Sorry, I was away for a week or so and have taken a while to get back to this. The quick answer is if your code is working, then the changes are fine.

However, a little explanation of what I think has happened here. In post #6 I posted my code with
Code: 
Option Base 1
just above my procedure. Having done that my array
AMatchCols = Array("J", "K", "N")
contains 3 values numbered 1, 2, and 3

When I posted the revised code in post #12, I forgot to re-post the Option Base 1 (though I still had it in my actual workbook). If you do not have the Option Base 1 in your Module, then the array
AMatchCols = Array("J", "K", "N")
still contains 3 values but they will be numbered 0, 1 and 2
and that is why you needed modifications.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
691
fjns
F
K
  • Question Question
Populate First Time Stamp For Staff
Replies
3
Views
669
DanteAmor
DanteAmor
E
  • Solved
Can someone tell me why the first column is not displaying all info?
Replies
3
Views
160
RoryA
RoryA
K
  • Question Question
Count Based On Date Range
Replies
8
Views
1K
kumara_faith
K
W
  • Question Question
Return Related URLs into one string (Textjoin)
Replies
1
Views
984
WilldoSEO
W

Forum statistics

Threads
1,224,518
Messages
6,179,256
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top