A working macro that I need to change the search range and write location

[Legacy 436997]

I have a working macro that I need help changing. I need to change the search range to numbers 1 to 10, and write the cell location.


I have 3 examples at the end and have uploaded my sample Excel workbook showing the expected result. Thank-you in advance for your assistance.




Notes for below:


- Need 2 decimal places on the left and 2 decimal places on the right in every cell.
- I’m using Excel 2010, Sheet 2.
- Please do not hard code I may need to make changes in the future.


Right now now the search is for a 15 but that has to change.

<code class="western">[SIZE=3]Sub DIVIDE()[/SIZE]</code>
<code class="western">Application.ScreenUpdating = False</code>

<code class="western">Dim pair As Variant, accumulator As Variant</code>
<code class="western">Dim findFifteen As Double</code>
<code class="western">Dim remainder As Long, found As Long</code>

<code class="western">found = 1</code>
<code class="western">    </code>
<code class="western">For Each pair In Range("B17:B26, F17:F26, J17:J26")</code>
<code class="western">    </code><code class="western">If Right(pair, 2) = 15 Then</code>
<code class="western">        </code><code class="western">If pair.Offset(0, 2) <= 12 Then</code>
<code class="western">            </code><code class="western">findFifteen = pair.Offset(0, 2) / 10</code>
<code class="western">            </code><code class="western">remainder = 0</code>
<code class="western">        </code><code class="western">Else</code>
<code class="western">            </code><code class="western">findFifteen = 1</code>
<code class="western">            </code><code class="western">remainder = pair.Offset(0, 2) Mod 10</code>
<code class="western">        </code><code class="western">End If</code>
<code class="western">        </code>
<code class="western">        </code><code class="western">For Each accumulator In Range("C7, E7, G7, I7, K7, C14, E14, G14, I14, K14")</code>
<code class="western">            </code><code class="western">If accumulator.Offset(-1, 0) = Val(Left(pair, InStr(pair, "-") - 1)) Then</code>
<code class="western">                </code><code class="western">accumulator.Value = accumulator.Value + remainder</code>
<code class="western">            </code><code class="western">End If</code>
<code class="western">            </code><code class="western">accumulator.Value = accumulator.Value + findFifteen</code>
<code class="western">        </code><code class="western">Next accumulator</code>
<code class="western">        </code><code class="western">End If</code>
<code class="western">Next pair</code>

<code class="western">Application.ScreenUpdating = True</code>
<code class="western">[SIZE=3]End Sub[/SIZE]</code>

1^st to do - is to find each possible number in the range and how it must be written. There will always be a positive result with a minimum of 1 and a maximum of 10 (could be expanded in the future) numbers found. For all numbers a positive result is the 3 cells to the right that have numbers in them.


Write specific cell location from the range B17:B26, F17:F26, J17:J26: For only numbers 1, 2, 3, 4 a positive result:



Number 1 from cell range, write cell location to cell B7. Divide the third cell by 10 cells (C7,E7,G7,I7,K7,C14,E14,G14,I14,K14) up to the number 12.00. After 12.00 the remainder would be written to C7. Add to any existing numbers in any of the cells.


Number 2 from cell range, write cell location to cell D7. Divide the third cell by 10 cells (C7,E7,G7,I7,K7,C14,E14,G14,I14,K14) up to the number 12.00. After 12.00 the remainder would be written to E7. Add to any existing numbers in any of the cells.


Number 3 from cell range, write cell location to cell F7. Divide the third cell by 10 (C7,E7,G7,I7,K7,C14,E14,G14,I14,K14) up to the number 12.00. After 12.00 the remainder would be written to G7. Add to any existing numbers in any of the cells.


Number 4 from cell range, write cell location to cell H7. Divide the third cell by 10 cells (C7,E7,G7,I7,K7,C14,E14,G14,I14,K14) up to the number 12.00. After 12.00 the remainder would be written to I7. Add to any existing numbers in any of the cells.


Found numbers 5, 6, 7, 8, 9, 10 with a positive result:


Number 5 from cell range, write cell location to cell J7. Write the number in the third cell to cell K7. Add to any existing numbers in any of the cells.
Number 6 from cell range, write cell location to cell B14. Write the number in the third cell to cell 14. Add to any existing numbers in any of the cells.
Number 7 from cell range, write cell location to cell D14. Write the number in the third cell to cell E14. Add to any existing numbers in any of the cells.
Number 8 from cell range, write cell location to cell F14. Write the number in the third cell to cell G14. Add to any existing numbers in any of the cells.
Number 9 from cell range, write cell location to cell H14. Write the number in the third cell to cell I14. Add to any existing numbers in any of the cells.
Number 10 from cell range, write cell location to cell J14. Write the number in the third cell to cell K14. Add to any existing numbers in any of the cells.




3 Examples with expected results – see sample Excel sheet


When the above code is run and the following would occur:


1^st Found number – Number 4
- Write B20 (number 4 cell location) to cell H7.
- Divide 10.00 located in cell E20 by 10 cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. Add to any existing numbers in the cell.


2^nd Found number - Number 9
- Write F18 (number 9 cell location) to cell H14.
- Write cell contents I18 (10.00) to cell I14, Add to any existing numbers in the cell.


3^rd Found Number - Number 1
- Write J26 (number 1 cell location) to cell B7.
- Divide 12.00 by 10 cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. The remaining 8.00 would be written to cell C7. Add to any existing numbers in the cell.




Thank-you in advance for any help you can provide.




<style type="text/css">pre { direction: ltr; color: rgb(0, 0, 10); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 10); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }code.western { font-family: "Liberation Mono", serif; }code.cjk { font-family: "WenQuanYi Micro Hei Mono"; }code.ctl { font-family: "Liberation Mono"; }</style>

 

[Legacy 436997]

To the moderator: could you please close this posting.
Thank-you
Christine