I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company "care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

maybe something like this:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"jamesea" wrote:

> I need to assign companies to one of four different numbers depending on
> where they fall in the alphabet, for example I need to assign a company "care
> homes ltd" as 01. Any ideas on the formula?
>
>
> 01 A GR
> 02 GS SA
> 03 SB THEC
> 04 THED Z
>

ignore my answer.. don't know what I was thinking!


--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

> maybe something like this:
>
> Function GetCompanyID(strCompany As String) As String
> If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
> Then
> GetCompanyID = "01"
> End If
> End Function
>
>
>
> --
>
> Hope that helps.
>
> Vergel Adriano
>
>
> "jamesea" wrote:
>
> > I need to assign companies to one of four different numbers depending on
> > where they fall in the alphabet, for example I need to assign a company "care
> > homes ltd" as 01. Any ideas on the formula?
> >
> >
> > 01 A GR
> > 02 GS SA
> > 03 SB THEC
> > 04 THED Z
> >

OK, something like this will work:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) >= "A" Or Left(UCase(strCompany), 2) <=
"GR" Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

> ignore my answer.. don't know what I was thinking!
>
>
> --
>
> Hope that helps.
>
> Vergel Adriano
>
>
> "Vergel Adriano" wrote:
>
> > maybe something like this:
> >
> > Function GetCompanyID(strCompany As String) As String
> > If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
> > Then
> > GetCompanyID = "01"
> > End If
> > End Function
> >
> >
> >
> > --
> >
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "jamesea" wrote:
> >
> > > I need to assign companies to one of four different numbers depending on
> > > where they fall in the alphabet, for example I need to assign a company "care
> > > homes ltd" as 01. Any ideas on the formula?
> > >
> > >
> > > 01 A GR
> > > 02 GS SA
> > > 03 SB THEC
> > > 04 THED Z
> > >

Below code assumes your sheet name is "sheet1",
column A is allocated for ID numbers, column B holds your names
and columns AA to AC are available
You can run this where ever you like.

rgds

Sub makeID()

Dim rng As Range
Dim i As Range
Dim rng1 As Range
Dim str1 As String
Dim rng2 As Range
Dim str2 As String
Dim rng3 As Range
Dim str3 As String

Application.ScreenUpdating = False

Set rng1 = Application.Intersect(Worksheets("sheet1") _
.Range("aa2:aa65526"), Worksheets("sheet1").UsedRange.EntireRow)
str1 = "=Substitute(B2,"" "","""")"
Set rng2 = Application.Intersect(Worksheets("sheet1") _
.Range("ab2:ab65526"), Worksheets("sheet1").UsedRange.EntireRow)
str2 = "=MID(aa2,1,4)"
Set rng3 = Application.Intersect(Worksheets("sheet1") _
.Range("ac2:ac65526"), Worksheets("sheet1").UsedRange.EntireRow)
str3 = "=Codeit(ab2)"

rng1.Formula = str1
rng2.Formula = str2

With Worksheets("sheet1")
Set rng = .Range("ab2", .Range("ab" & Rows.Count). _
End(xlUp))
End With

For Each i In rng
Select Case Len(i.Value)
Case 1: i.Value = i.Value & "aaa"
Case 2: i.Value = i.Value & "aa"
Case 3: i.Value = i.Value & "a"
End Select
Next i

rng3.Formula = str3

With Worksheets("sheet1")
Set rng = .Range("ac2", .Range("ac" & Rows.Count). _
End(xlUp))
End With

For Each i In rng ' below numbers are your hardcoded sections
Select Case i.Value
Case 65656565 To 71829090
Worksheets("sheet1").Range("A" & i.Row) = 1
Case 71836565 To 83659090
Worksheets("sheet1").Range("A" & i.Row) = 2
Case 83666565 To 84726967
Worksheets("sheet1").Range("A" & i.Row) = 3
Case 84726968 To 90909090
Worksheets("sheet1").Range("A" & i.Row) = 4
End Select
Next i

Worksheets("sheet1").Range("aa1.ac1").EntireColumn. _
Delete

Application.ScreenUpdating = True

End Sub


Pls put below function on a standart module ( Function originally
coded by Frank Isaac )

Function CodeIt(rngName As Excel.Range) As String
Dim iX As Integer, iVal As Integer
For iX = 1 To Len(rngName)
iVal = Asc(UCase(Mid(rngName, iX, 1))) ' - 64
If iVal < 65 Or iVal > 90 Then
CodeIt = CodeIt & "0"
Else
CodeIt = CodeIt & CStr(iVal)
End If
Next
End Function

For a VBA solution, maybe this. Adjust depending on how/where your company
ID data is stored. This assumes the structure as you have shown:

Public Function GetCompanyID(WhichCompany As String) As String
Dim CompIDData As Variant
Dim IDNum As Long

CompIDData = Range("rngCompIDs").Value

For IDNum = LBound(CompIDData, 1) To UBound(CompIDData, 1)
If (UCase(Left(WhichCompany, Len(CompIDData(IDNum, 2)))) >=
CompIDData(IDNum, 2)) And (UCase(Left(WhichCompany, Len(CompIDData(IDNum,
3)))) <= CompIDData(IDNum, 3)) Then
GetCompanyID = CompIDData(IDNum, 1)
Exit Function
End If
Next

GetCompanyID = "#N/A"

End Function

However, you could use worksheets functions instead

NickHK

"jamesea" <(E-Mail Removed)> wrote in message
news:98E97CC0-94E6-4D43-8E27-(E-Mail Removed)...
> I need to assign companies to one of four different numbers depending on
> where they fall in the alphabet, for example I need to assign a company
"care
> homes ltd" as 01. Any ideas on the formula?
>
>
> 01 A GR
> 02 GS SA
> 03 SB THEC
> 04 THED Z
>

Thank you very much Vergel & Nick. You helped me as well.

So it is only "=GetID(B2)" on A column.

Kind regards


Public Function GetID(Company As String) As String
Select Case UCase(Company)
Case "A" To "GRZZZZZZZZZZZZZZZZZ": GetID = 1
Case "GS" To "SAZZZZZZZZZZZZZZZZ": GetID = 2
Case "SB" To "THECZZZZZZZZZZZZZZ": GetID = 3
Case "THED" To "ZZZZZZZZZZZZZZZZ": GetID = 4
End Select
End Function