So the problem im solving is as follows:
Im comparing a list of company names which are missing legal form and city(but sometimes they have a city or legal form or both), and a list of properly formated names, and then returning a properly formated name. Ive adapted wellsr substr code to make this:
Function proper(text As String) As String
Dim lastrow As Long
Dim i As Integer
lastrow = Sheets("sorted").Range("A30000").End(xlUp).Row
For i = 1 To lastrow
If InStr(1, LCase(text), LCase(Sheets("sorted").Range("A" & i)), vbTextCompare) <> 0 Then
proper = Sheets("sorted").Range("D" & i).Value
End If
Next i
End Function
Range A in "sorted" sheet containes only company name which it compares to unsorted string (because unsorted string is always longer) and returns value from D range which has proper names.
Its working well but its making some errors and I need help improving it
What I need help with is
1. What value does function return when it doesnt find anything, and can i make it return "nothing" for eg. if it doesnt find substring. Currently when it doesnt find anything it just returns blank cell with formula in it and im dumbfounded to how to adapt it to return something more descriptive.
2. Optimise the search. Try to search first those words which in sorted list start on the same letter as the word im trying to find, then if not found expand the search to entire list.
This is to try and eliminate errors where for eg im searching for "AirBus" and it returns Bus inc., Luxemburg.(note that im searching in reverse, comparing proper names to the one im trying to find).
Im comparing a list of company names which are missing legal form and city(but sometimes they have a city or legal form or both), and a list of properly formated names, and then returning a properly formated name. Ive adapted wellsr substr code to make this:
Function proper(text As String) As String
Dim lastrow As Long
Dim i As Integer
lastrow = Sheets("sorted").Range("A30000").End(xlUp).Row
For i = 1 To lastrow
If InStr(1, LCase(text), LCase(Sheets("sorted").Range("A" & i)), vbTextCompare) <> 0 Then
proper = Sheets("sorted").Range("D" & i).Value
End If
Next i
End Function
Range A in "sorted" sheet containes only company name which it compares to unsorted string (because unsorted string is always longer) and returns value from D range which has proper names.
Its working well but its making some errors and I need help improving it
What I need help with is
1. What value does function return when it doesnt find anything, and can i make it return "nothing" for eg. if it doesnt find substring. Currently when it doesnt find anything it just returns blank cell with formula in it and im dumbfounded to how to adapt it to return something more descriptive.
2. Optimise the search. Try to search first those words which in sorted list start on the same letter as the word im trying to find, then if not found expand the search to entire list.
This is to try and eliminate errors where for eg im searching for "AirBus" and it returns Bus inc., Luxemburg.(note that im searching in reverse, comparing proper names to the one im trying to find).
