How to locate numbers 1 to 10 in a range

Category: [Excel] Demo Available

How to locate numbers 1 to 10 in a range

Requirement:

Referencing to my sample Excel sheet below the user needs code to locate all numbers from 1 to 10 in the range G3:G12 (the user needs to be able to add/move additional ranges and change this sample range). A positive result would be finding any set of numbers (1-1, 9-9, 10-1 etc.) in the cell adjacent to the right of numbers 1 to 10.

Example in my Excel sheet below

The first found number would be 1 in cell F3 and the set of numbers would be 4-1 located in cell G3. Using the found number, 1, the code would then search the range A3:A12 (I will need to be able to add or change additional ranges) for the 1 and then write the cell location in the cell to the right of it B3 in this example. The second example in cell F9, 7 with a set of numbers of 10-2 in cell G9 would write as F9 in cell B9.

The set of number cells are formatted as text.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1216598-locate-numbers-1-to-10

Solution:

Code:

Sub scottparker()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, fnd As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("F3:F" & LastRow)
        If rng <> "" And rng.Offset(0, 1) <> "" Then
            Set fnd = Range("A3:A" & LastRow).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                fnd.Offset(0, 1) = rng.Address(0, 0)
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.