Results 1 to 2 of 2

Change search range and search criteria in my code

Threaded View

scott1945 Change search range and... 12-06-2018, 10:37 PM
scott1945 Re: Change search range and... 12-10-2018, 02:53 PM
  1. #1
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    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.



    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create User form to perform search using multiple search criteria
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2015, 06:16 AM
  2. Change Search Criteria
    By mfjanoo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2014, 03:53 PM
  3. Replies: 4
    Last Post: 05-20-2014, 10:03 AM
  4. VBA Code - Search text & search number & search qty and result - Urgent Please
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 10:51 AM
  5. Replies: 1
    Last Post: 10-18-2012, 05:52 AM
  6. Search all worksheets and paste all rows w/search criteria to single sheet
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-22-2012, 01:31 PM
  7. Search Multiple Worksheets Against List of Non-Exact Search Criteria?
    By thump4r in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 03:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1