how to get Corresponding cell value

[johnbest]

hi friends

I have 2 cols displaying name and work done like
Col A Col B
-------------
James 20
Jack 21

If a user input Col B value in an InputBox then
how should I get corresponding cell value?

thanks in advance
johnbes

 

[CLR]

If you do Data > Filter > AutoFilter, and select 20 from the dropdown arrow
at the top of column B, it will filter and show all of those names who have
a 20 in column B (there may be more than one), Data > Filter > Autofilter
again to clear the filter and return to normal....

Vaya con Dios,
Chuck, CABGx3

 

[johnbest]

thanks

but how to do it in VBA?

thanks in advance
johnbes

 

[Dave Peterson]

One way is to use .find to find that value and then just use offset to get the
value:

Option Explicit
Sub testme()

Dim myAns As String
Dim FoundCell As Range
Dim wks As Worksheet

myAns = InputBox(Prompt:="enter the value")
If myAns = "" Then Exit Sub

Set wks = Worksheets("sheet1")

With wks
With .Range("B:b")
Set FoundCell = .Find(what:=myAns, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "Input not found"
Else
MsgBox FoundCell.Offset(0, -1).Value
'or whatever you want here
End If
End With

End Sub

 

[CLR]

This is modified from Dave's code to use the Autofilter to display all
repetitions of the selected value, should there be more than
one...........such as:

James 20
Jack 21
Sally 20


Sub AutoFilterFromBox()
Dim BoxAns As String
BoxAns = InputBox(Prompt:="Enter Col B value")
If BoxAns = "" Then Exit Sub
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=BoxAns
End Sub



Vaya con Dios,
Chuck, CABGx3