PDA

View Full Version : Can this code be simplified



Djblois
07-20-2010, 08:57 AM
This is the code that I have created to determine the company that the report references. It is very straight forward so I do not think I need to explain how it works. However, I am wondering if there is a way to simplify it with elseifs or Select Case or any other way.


Set findstring = Range("A" & finalrow(ActiveSheet)).Find(What:="Atalanta Co.")

If Not findstring Is Nothing Then
SaveSetting "BRT", "General", "Current Company", "Atalanta"
GoTo endSub
End If

Set findstring = Range("A" & finalrow(ActiveSheet)).Find(What:="Camerican International, Inc.")

If Not findstring Is Nothing Then
SaveSetting "BRT", "General", "Current Company", "Camerican"
GoTo endSub
End If

Set findstring = Range("A" & finalrow(ActiveSheet)).Find(What:="De medici")

If Not findstring Is Nothing Then
SaveSetting "BRT", "General", "Current Company", "Demedici"
GoTo endSub
End If

Set findstring = Range("A" & finalrow(ActiveSheet)).Find(What:="Finica Food Specialties Ltd.")

If Not findstring Is Nothing Then
SaveSetting "BRT", "General", "Current Company", "Finica"
GoTo endSub
End If

p45cal
07-20-2010, 10:14 AM
Select Case Range("A" & finalrow(ActiveSheet)).Value
Case "Atalanta Co."
SaveSetting "BRT", "General", "Current Company", "Atalanta"
Case "Camerican International, Inc."
SaveSetting "BRT", "General", "Current Company", "Camerican"
Case "De medici"
SaveSetting "BRT", "General", "Current Company", "Demedici"
Case "Finica Food Specialties Ltd."
SaveSetting "BRT", "General", "Current Company", "Finica"
End Select
? (untested)
If the company's name saved to the app's settings is always just the first word, it could be shorter. I've presumed that finalrow returns just a single number.

Or..CoName = "" 'this line not required if you use the Case Else option below.
Select Case Range("A" & finalrow(ActiveSheet)).Value
Case "Atalanta Co."
CoName = "Atalanta"
Case "Camerican International, Inc."
CoName = "Camerican"
Case "De medici"
CoName = "Demedici"
Case "Finica Food Specialties Ltd."
CoName = "Finica"
'Case Else 'if you use this option you can delete the 'If CoName <> "" Then' part of the line below
'CoName = "Unrecognised Co.name"
End Select
If CoName <> "" Then SaveSetting "BRT", "General", "Current Company", CoName

mdmackillop
07-20-2010, 10:24 AM
Try

Sub Test()
Dim ToFind
Dim ToSet
Dim i As Long
ToFind = Array("Atalanta Co.", "Camerican International, Inc.", "De medici", "Finica Food Specialties Ltd.")
ToSet = Array("Atalanta", "Camerican", "Demedici", "Finica")

For i = 0 To 4
Set findstring = Range("A" & finalrow(ActiveSheet)).Find(What:=ToFind(i))
If Not findstring Is Nothing Then
SaveSetting "BRT", "General", "Current Company", ToSet(i)
GoTo endSub
End If
Next
End Sub