Find Value in Column (Text and Numbers)

[rhombus4]

Hi

Trying to find a Value in a Column using code below

The column contains text, numbers that are actually text and some numbers. Need it to find Text, find numbers if they are text and numbers
Also need it to show the value to the right of it in col D in a message box

  If Not IsError(Application.Match(valSearch, ws.Range("B:B"), 0)) Then
    MsgBox "Found"
    Exit Sub
  End If
    MsgBox "Not Found"

 

[jasonb75]

Try using the range find method instead of application match

Dim ws As Worksheet, rng As Range, valsearch As Variant
valsearch = 345
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("B:B").Find(valsearch, , xlValues, xlWhole, , , False, False, False)
If Not rng Is Nothing Then
    MsgBox "found"
    Exit Sub
Else
    MsgBox "not found"
End If

 

[dmt32]

Hi

Trying to find a Value in a Column using code below

The column contains text, numbers that are actually text and some numbers. Need it to find Text, find numbers if they are text and numbers
Also need it to show the value to the right of it in col D in a message box

Hi,
see if this update to your code does what you want

Dim valSearch   As Variant, m As Variant

    valSearch = "1234"
    
    If IsNumeric(valSearch) Then valSearch = Val(valSearch)
    m = Application.Match(valSearch, ws.Range("B:B"), 0)
    
    If Not IsError(m) Then
        MsgBox ws.Cells(CLng(m), "D").Value & Chr(10) & "Found"
    Else
        MsgBox "Not Found"
    End If

Dave

 

[rhombus4]

Try using the range find method instead of application match

Dim ws As Worksheet, rng As Range, valsearch As Variant
valsearch = 345
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("B:B").Find(valsearch, , xlValues, xlWhole, , , False, False, False)
If Not rng Is Nothing Then
    MsgBox "found"
    Exit Sub
Else
    MsgBox "not found"
End If

Thanks, Just need to show the Value in D if the Value is found

Should it make a difference if valsearch = 345 or valsearch = "345"
Also Does it make a difference if I changed to as String instead of as Variant as that seemed to work as well

 

[rhombus4]

Hi,
see if this update to your code does what you want

Dim valSearch   As Variant, m As Variant

    valSearch = "1234"
   
    If IsNumeric(valSearch) Then valSearch = Val(valSearch)
    m = Application.Match(valSearch, ws.Range("B:B"), 0)
   
    If Not IsError(m) Then
        MsgBox ws.Cells(CLng(m), "D").Value & Chr(10) & "Found"
    Else
        MsgBox "Not Found"
    End If

Dave

Hi

Doesnt seem to work if the value appears as a number but is actually text

 

[jasonb75]

Sorry, I missed the bit about adding D to the message box

Dim ws As Worksheet, rng As Range, valsearch As Variant
valsearch = 345
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("B:B").Find(valsearch, , xlValues, xlWhole, , , False, False, False)
If Not rng Is Nothing Then
    MsgBox rng.Offset(, 2).Value & Chr(10) & "found"
    Exit Sub
Else
    MsgBox "not found"
End If

I would personally leave the declaration as variant. It should work with string, but there may be exceptions that are not coming to mind. Variant should allow for all possibilities.

 

[rhombus4]

Sorry, I missed the bit about adding D to the message box

Dim ws As Worksheet, rng As Range, valsearch As Variant
valsearch = 345
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("B:B").Find(valsearch, , xlValues, xlWhole, , , False, False, False)
If Not rng Is Nothing Then
    MsgBox rng.Offset(, 2).Value & Chr(10) & "found"
    Exit Sub
Else
    MsgBox "not found"
End If

I would personally leave the declaration as variant. It should work with string, but there may be exceptions that are not coming to mind. Variant should allow for all possibilities.

Thanks for the explanation and update

 

[Caliexceldude]

Hello, does anyone have a good way to do this conditional with function:

(if text "Lead" is found on cell G2, then sum X2+1, (if text "Field", is found on cell G2, then B2+1.5, B2) )

trying to do this formula in one cell and copy it down to multiple on a raw datasheet

Thanks

 

[Caliexceldude]

Hello, does anyone have a good way to do this conditional with function:

(if text "Lead" is found on cell G2, then sum X2+1, (if text "Field", is found on cell G2, then B2+1.5, B2) )

trying to do this formula in one cell and copy it down to multiple on a raw datasheet

Thanks


View attachment 51114

Oh and if the job title includes the words "lead" and "Field" then column X= 2.5 , based on logic above

 

[Fluff]

@Caliexceldude as this is a totally different question to the op, please start a thread of your own. Thanks