Change search range and search criteria in my code

I have a macro, below, that I would like help with changing the following:

1st change – Change search range to: B17:B26, F17:F26, J17:J26.
2nd change - Currently the code searches for a 15 in the last number which I would like to change to search for numbers 1 to 10.
3rd change - Lastly, I would like to change the criteria for dividing cell contents, see below numbers 1 to 10.

I have 3 examples at the end and have uploaded my sample Excel sheet and also posted my sheet image.

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.


See sample Excel sheet for reference

Below is each possible found number in the range and how it must be written, see 3 examples below if not clear)

Number 1 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell B7.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell B6 to reference column location.
- Divide the number (located in columns E/I/M, 3 cells to the right of the found number) by 10 up to the number 12.00. Write to cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. After 12.00 place remaining numbers in cell C7.

Number 2 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell D7.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell D6 to reference column location.
- Divide the number (located in columns E/I/M, 3 cells to the right of the found number) by 10 up to the number 12.00. Write to cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. After 12.00 place remaining numbers in cell E7.

Number 3 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell F7.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell F6 to reference column location.
- Divide the number (located in columns E/I/M, 3 cells to the right of the found number) by 10 up to the number 12.00. Write to cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. After 12.00 place remaining numbers in cell G7.

Number 4 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell H7.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell H6 to reference column location.
- Divide the number (located in columns E/I/M, 3 cells to the right of the found number) by 10 up to the number 12.00. Write to cells C7,E7,G7,I7,K7,C14,E14,G14,I14,K14. After 12.00 place remaining numbers in cell I7.

Numbers 5 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell J7.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell J6 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell K7.

Numbers 6 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell B14.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell B13 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell C14.

Numbers 7 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell D14.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell D13 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell E14.

Numbers 8 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell F14.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell F13 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell G14.

Numbers 9 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell H14.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell H13 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell I14.

Numbers 10 with a positive result:
- Write specific cell location from the following range: B17:B26, F17:F26, J17:J26 to cell J14.
- Write an A (cell B16), B (cell F16) or C (cell J16) to cell J13 to reference column location.
- Write the number (located in columns E/I/M, 3 cells to the right of the found number) to cell K14.



3 Examples with expected results – see ample Excel sheet

The above code is run and the following would occur:

1st Found number – Column A, Number 4
- Write B16 (A location) to cell H6.
- Write B20 (number 4 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.

2nd Found number - Column B, Number 9
- Write F16 (B location) to cell H13.
- 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.

3rd Found Number - Column C, Number 1
- Write J16 (C location) to cell B6.
- 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.

Please Login or Register  to view this content.

sample excel sheet 6.jpeg

To the moderator: could you close this posting. I have some reworking to do on this post.