Hi Friends,
I am getting an error while working on below code:
Private Sub cmdsearch_Click()
Dim a(), r As Range, res, i As Long, ff As String, rng As Range
Dim title
title = Array("Unit Description,Unit Code,Roll Code")
res = Me.txtsearch
If Len(res) = 0 Then
Here:
Application.EnableEvents = False
With Me.ListBox1
.ColumnHeads = False
.RowSource = ""
.Clear
End With
Me.txtsearch.SetFocus
Me.cmdsearch.Enabled = True
Application.EnableEvents = True
Exit Sub
End If
With Me
.cmdsearch.Enabled = False
End With
With Sheets("Data")
Set r = .Range("DataRange").Find(What:=res, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)
If Not r Is Nothing Then
ReDim a(1 To 4, 1 To 1): i = 1
faddress = r.Address: ReDim Preserve a(1 To 4, 1 To i)
For ii = 1 To 4
a(ii, i) = .Cells(r.Row, ii).Value
If ii = 4 Then
a(ii, i) = r.Row
End If
Next
Do
Set r = .Range("DataRange").FindNext(r)
If r.Address = faddress Then Exit Do
i = i + 1: ReDim Preserve a(1 To 4, 1 To i)
For ii = 1 To 4
a(ii, i) = .Cells(r.Row, ii).Value
If ii = 4 Then
a(ii, i) = r.Row
End If
Next
Loop Until r Is Nothing
Else
MsgBox "DATA DOES NOT EXIST!", vbInformation + vbOKOnly, "Alert Message"
Me.txtsearch = ""
GoTo Here
End If
End With
noclick = True
With Me.ListBox1
.ColumnCount = 4
.ColumnWidths = "270;100;100;0"
If i > 1 Then
.List = Application.Transpose(a)
Else
.Column = a
End If
End With
noclick = False
End Sub
This above code search the value and display the entire row of matched value from one sheet.
Now what i want is this it should search in multiple sheet and display same (the entire row of matched value)
Can anyone help me out in this.
Regards,
Harindra Devadiga
Bookmarks