Finding a number based on criteria

[AAP]

Hi Friends,

Looking a way to find a number from a column. If that number is not available in the column then below that number. For example: I want to find 14 in a column (the first instance) then I need address of that cell and if 14 is not present then find the cell address of first instance of <14 number (could be 13 or 12 or 11 which ever is available before 14).

In addition, How can I read unique values only from that column to an array. I would like to show as a message to the user.

A sample file is attached.

Thanks

Kind Regards
AAP

 

[Chihiro]

So in your sample what should be returned if 14 is used?

It's bit unclear what final result should be...

To read unique value only, use Scripting.Dictionary.

 

[AAP]

So in your sample what should be returned if 14 is used?

It's bit unclear what final result should be...

To read unique value only, use Scripting.Dictionary.

If 14 is not found then go for <14 number (could be 13 or 12 or 11 which ever is available before 14).

 

[Chihiro]

Repeating what you wrote in initial post doesn't make it any clearer than before. I asked the question, since initial post wasn't enough to fully understand your requirement.

<14 applies to any number between 1 to 13... So what's the condition exactly to decide which is valid answer?

 

[AAP]

Sorry for misunderstanding. In sample file If I want to find 14 then it is not there which means excel will give me address of number between 1 to 13. i.e. excel search for 14 if not found then go for 13 if not found then go for 12 and so on...until 1 to give me at least one cell address.

 

[Chihiro]

So, you want the cell address and not the number? Also, are you looking for first instance of the number found only? Or all instance. Code will significantly change based on what the end result should be.

At any rate, try below demo.

Sub Demo()
Dim sRange As Range, c As Range
Dim sVal
Set sRange = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

Step1:
sVal = Application.InputBox("Enter a number to search", Type:=1)
cVal = sVal
If Not IsNumeric(sVal) Or sVal = vbNullString Then GoTo Step1

With sRange
    Do
    Set c = .Find(sVal, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
    sVal = sVal - 1
    Loop While c Is Nothing And sVal >= 0
  
    If sVal < 0 Or c Is Nothing Then
        MsgBox "Match not found"
    Else
        MsgBox "Value " & c.Value & " which is " & cVal - c.Value & " less than " & cVal & " is found at " & c.Address
    End If
  
End With
  
End Sub

 

[AAP]

So, you want the cell address and not the number? Also, are you looking for first instance of the number found only? Or all instance. Code will significantly change based on what the end result should be.

At any rate, try below demo.

Sub Demo()
Dim sRange As Range, c As Range
Dim sVal
Set sRange = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

Step1:
sVal = Application.InputBox("Enter a number to search", Type:=1)
cVal = sVal
If Not IsNumeric(sVal) Or sVal = vbNullString Then GoTo Step1

With sRange
    Do
    Set c = .Find(sVal, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
    sVal = sVal - 1
    Loop While c Is Nothing And sVal >= 0
  
    If sVal < 0 And c Is Nothing Then
        MsgBox "Match not found"
    Else
        MsgBox "Value " & c.Value & " which is " & cVal - c.Value & " less than " & cVal & " is found at " & c.Address
    End If
  
End With
  
End Sub

Great!
Yes I wanted cell address and first instance. Perfect solution.
Can you also give me solution for scripting dictionary to find unique values and read in an array?

 

[Chihiro]

That'd be separate from .Find solution.

See sample below.

Sub dicDemo()
Dim cel As Range
Dim Key
Dim resStr As String
With CreateObject("Scripting.Dictionary")
    For Each cel In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
        If Not .Exists(cel.Value) Then
            .Add Key:=cel.Value, Item:=cel.Address
        End If
    Next
    resStr = "---First Instance of Unique # found---" & vbNewLine
    For Each Key In .Keys
        resStr = resStr & Key & " found at " & .Item(Key) & vbNewLine
    Next
    MsgBox resStr
End With
End Sub

 

[AAP]

That'd be separate from .Find solution.

See sample below.

Sub dicDemo()
Dim cel As Range
Dim Key
Dim resStr As String
With CreateObject("Scripting.Dictionary")
    For Each cel In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
        If Not .Exists(cel.Value) Then
            .Add Key:=cel.Value, Item:=cel.Address
        End If
    Next
    resStr = "---First Instance of Unique # found---" & vbNewLine
    For Each Key In .Keys
        resStr = resStr & Key & " found at " & .Item(Key) & vbNewLine
    Next
    MsgBox resStr
End With
End Sub

Works great
Many many thanks