Dear friends

I want to write a macro which search in 3 column and match searching
criteria

A B C
1 Pravin Kumar Jain

My data is stored in above format and i want search want to search
"Pravin Kumar Jain" and it should match. Please help me to which
function I should use.

Thank you in advance


from
Murlidhar

If the data were small, I think I'd just loop through the rows looking for a
match in all three columns.

If the data were large, I think I'd use .Find() to look for a match in one
column and then check the other two columns.

If I were trying to bring back a field based on all 3 matches, I would use a
formula instead.

dim myCell as range
dim myRng as range
with worksheets("tablesheetnamehere")
set myrng = .range("A1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if lcase(mycell.value) = lcase(valuetocheckcolumnA) then
if lcase(mycell.offset(0,1).value) = lcase(valuetocheckcolumnB) then
if lcase(mycell.offset(0,2).value) = lcase(valuetocheckcolumnC) then
'found a match--what happens next
end if
end if
end if
next mycell

bmurlidhar wrote:

Dear friends

I want to write a macro which search in 3 column and match searching
criteria

A B C
1 Pravin Kumar Jain

My data is stored in above format and i want search want to search
"Pravin Kumar Jain" and it should match. Please help me to which
function I should use.

Thank you in advance

from
Murlidhar

--

Dave Peterson

This macro should work for you under the rules you've laid out: search
columns A, B and C for a 3-name/3-word entry.

Sub Search3Columns()
Dim searchString As String
Dim namesToFind As Variant
Dim searchRange As Range
Dim anySearchCell As Range
Dim matchFound As Boolean
Dim LC As Integer

searchString = InputBox$("Enter the search name:", "Search 3 Columns", "")
If searchString = "" Then
'nothing entered, quit.
Exit Sub
End If
'namesToFind will be an array as namesToFind(0) through (2)
namesToFind = Split(searchString, " ", 3)
'remove leading/trailing "white space" and
'convert to all UPPERCASE for more positive matching
For LC = LBound(namesToFind) To UBound(namesToFind)
namesToFind(LC) = UCase(Trim(namesToFind(LC)))
Next
Set searchRange = ActiveSheet.Range("A1:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
For Each anySearchCell In searchRange
If UCase(Trim(anySearchCell)) = namesToFind(0) And _
UCase(Trim(anySearchCell.Offset(0, 1))) = namesToFind(1) And _
UCase(Trim(anySearchCell.Offset(0, 2))) = namesToFind(2) Then
anySearchCell.Select
matchFound = True
Exit For
End If
Next
Set searchRange = Nothing
If Not matchFound Then
MsgBox "No match found for '" & searchString & "'"
End If
End Sub


"bmurlidhar" wrote:

Dear friends

I want to write a macro which search in 3 column and match searching
criteria

A B C
1 Pravin Kumar Jain

My data is stored in above format and i want search want to search
"Pravin Kumar Jain" and it should match. Please help me to which
function I should use.

Thank you in advance


from
Murlidhar
.