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.
Sub Find3()
Application.ScreenUpdating = False
Dim pair As Variant, accumulator As Variant
Dim findFifteen As Double
Dim remainder As Long, found As Long
found = 1
For Each pair In Range("H17, J17, L17, N17, P17")
If Right(pair, 2) = 15 Then
If pair.Offset(0, 3) <= 12 Then
findFifteen = pair.Offset(0, 3) / 10
remainder = 0
Else
findFifteen = 1
remainder = pair.Offset(0, 3) Mod 10
End If
For Each accumulator In Range("H17, J17, L17, N17, P17")
If accumulator.Offset(-1, 0) = Val(Left(pair, InStr(pair, "-") - 1)) Then (my note,orginal from website: “Then” goes after – 1)) in above line)
accumulator.Value = accumulator.Value + remainder
End If
accumulator.Value = accumulator.Value + findFifteen
Next accumulator
Range("E" & found).Value = pair
found = found + 1
End If
Next pair
Application.ScreenUpdating = True
End Sub
sample excel sheet 6.jpeg
Bookmarks