ashmita
09-07-2021, 11:40 PM
Hello Everyone,
The below script has been developed to search any keyword in the excel workbook and retrieve the result.
My requirement is to add a check point to retrieve data based on particular company.
Therefore I have created 4 button for each company within the sheet and would require only those data based on selecting that company.
Can anyone please help me.
Sub Worksheet_Change(ByVal Target As Range)
Dim Fnd As Range, Crit As String
If Not Intersect(Target, Range("H11")) Is Nothing Then
Rows(14 & ":" & Rows.Count).Delete
If Target = "" Then Exit Sub
Crit = IIf(IsNumeric(Target), Target, "*" & Target & "*")
With Sheets("Data").Cells(1).CurrentRegion
Set Fnd = .Cells.Find(Crit, , xlValues, xlPart)
If Not Fnd Is Nothing Then
.AutoFilter Fnd.Column, Fnd
Union(.Columns("A:B"), .Columns("H:I"), .Columns("N")).Offset(1).Copy Range("G14")
Range("G14").CurrentRegion.Borders.Weight = 2
Else
MsgBox "NOT FOUND", vbInformation, ""
End If
.AutoFilter
End With
End If
End Sub
The below script has been developed to search any keyword in the excel workbook and retrieve the result.
My requirement is to add a check point to retrieve data based on particular company.
Therefore I have created 4 button for each company within the sheet and would require only those data based on selecting that company.
Can anyone please help me.
Sub Worksheet_Change(ByVal Target As Range)
Dim Fnd As Range, Crit As String
If Not Intersect(Target, Range("H11")) Is Nothing Then
Rows(14 & ":" & Rows.Count).Delete
If Target = "" Then Exit Sub
Crit = IIf(IsNumeric(Target), Target, "*" & Target & "*")
With Sheets("Data").Cells(1).CurrentRegion
Set Fnd = .Cells.Find(Crit, , xlValues, xlPart)
If Not Fnd Is Nothing Then
.AutoFilter Fnd.Column, Fnd
Union(.Columns("A:B"), .Columns("H:I"), .Columns("N")).Offset(1).Copy Range("G14")
Range("G14").CurrentRegion.Borders.Weight = 2
Else
MsgBox "NOT FOUND", vbInformation, ""
End If
.AutoFilter
End With
End If
End Sub