As am new to VBA, I will be thankful if anyo can help me with this.
I want to sort rows based on the presence or absence of a specific text in the whole string.
Range B Contains:
Audi Car, Suzuki Bike, Honda Car, Volvo Bus, Benz Car, Yamaha Bike
So I want VBA code which sorts based on if its a CAR, BIKE or BUS.
After Executing, Range B should be sorted this way:
Audi Car, Benz Car, Honda Car, Suzuki Bike, Yamaha Bike, Volvo Bus
Please help.
If I where to handle a problem like this I would first make an array with all the different entities. The next thing you can do is to use the InStr(startsearchingposition, String, searchforvalue). The InStr method returns the startposition of searchvalue in String, else it returns 0 or NUll. So if it has a record the value of Instr returns is greater than 1.
Dim myArray(1 to 3) As Variant, i, number_of_element As Integer, y as String
Dim ws As Worksheet: Set ws = ActiveSheet
myArray(1) = "Car"
myArraY(2) = "Bus"
myArraY(3) = "Bike"
'Lets assume that all you data is in column A
For i = 1 To ws.Cells(rows.count, 1).end(xlup).row
y = ws.Cells(i,1).value
number_of_element = 1
For Each element in myArray
If InStr(1, y, element) > 1 Then
ws.cells(i, number_of_element +1).value = y
Exit For
Else
number_of_element = number_of_element + 1
End if
Next
Next i
Maybe this doens't completely satisfied you criteria but at least all the different entities are now parted. The Next thing you could do is sort all the elements in every entity en put them back together.
VBA has some useful methods that can be used to parse a certain string. Mid() and InStr() are the two I use the most.
use Macro to record the following steps:
1.paste data vertically in column A (paste special - transpose)
2.insert function Split by Space to column B (return value eg: bike, car)
3.sort column A and B in the order you like
4.copy column A and paste special - transpose
5.stop Macro recording, change all cells (eg: .range(row,col))
