Fill one column based on particular strings in another column
I’m posting my query for the first time. Sorry if it has been repeated. I searched a lot but I didn’t get what I wanted.
I’ve some company names in A column and I want to fill column B with some key companies ( with condition like it contains “AND” , PVT, Deep . These are examples)
Actualluy mu file contains 20K to 30K rows with data in it and I have to find 35 key compnies and fill the requierd column.
I created a sample Macro but not working:
Sub search()
Dim i As Integer
Dim testvalue As Integer
Dim lastusedrow As Integer
Dim srch As String
lastusedrow = Worksheets("Sheet3").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
For i = 2 To lastusedrow
srch = "and"
testvalue = WorksheetFunction.search(srch, Cells(i, "A"))
If testvalue > 0 Then
Cells(i, "B").Value = "Bajra AND"
End If
Next i
End Sub
Name Key Clients
INVESTMENTS PVT LTD
BAJRA AND BAJRA ENT Bajra AND (Searched Bajra)
BAJRA AND BAJRA ENT Bajra AND
BAJRA AND BAJRA ENT Bajra AND
JHULLAL TRADEE
INTERIORS PVT LTD.
INTERIORS PVT LTD.
dva Hospitality LLP DVA (Searched DVA)
SUPPLIERS PVT. LTD. Mosaic (Searched Suppliers)
SUPPLIERS PVT. LTD. Mosaic
SUPPLIERS LTD. Mosaic
SUPPLIERS LTD. Mosaic
DEEP SANITARY
DEEP SANITARY
Bajaj Ltd
Bajaj Ltd
Bajaj Ltd
Thanks & Regards
Satish R Lakra
srlakra@gmail.com // +91 8800 576 333
Try this
Here you go!
Sub search()
Dim i As Integer
Dim testvalue As Integer
Dim lastusedrow As Integer
Dim srch As String
lastusedrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastusedrow
srch = "and"
testvalue = InStr(UCase(Cells(i, 1)), UCase(srch))
If testvalue > 0 Then
Cells(i, 2).Value = "Bajra AND"
End If
Next i
End Sub
Let me know if there are any issues, it seems to be working fine for me. Sincerely, -Max