area code
 

I have one column that has list of phone numbers with area code. I
need to make new column for which state. I would like to make a small
macro program to check the area code and put two letters state on new
column. How do we do that. I was thinking to use loop for read each
row and use if area code = (301) then row 1 = CA elseif area code =
(302) then row 1 = UT.... I don't know where to start..

area code
 

if it was me, i use something like this:

found this using google
http://www.bennetyee.org/ucsd-pages/area.html

copy and paste the columns in a worksheet
delete everything but the first 2 columns
would leave something like this:
201 NJ
202 DC
203 CT
204 MB
205 AL
206 WA


so i have a1:b384 populated. i have my phone numbers in column F and the state
code will go in column G, right next to it. i assumed the area codes were in
().

then i'd use code to find the values, change the ranges to whatever you need

Option Explicit
Sub area_codes()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim rngfound As Range
Dim lastrow As Long
Dim lastrow2 As Long
Dim cval As String
Dim cell As Range
Dim firstaddress As String
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = ws.Cells(Rows.Count, "f").End(xlUp).Row
Set rng = ws.Range("A1:A" & lastrow)
Set rng2 = ws.Range("F1:F" & lastrow2)

For Each cell In rng2
cval = Mid(cell.Value, 2, 3)
Set rngfound = rng.Find(cval, LookIn:=xlValues)
If Not rngfound Is Nothing Then
firstaddress = rngfound.Address
Do
cell.Offset(0, 1).Value = rngfound.Offset(0, 1).Value
Set rngfound = rng.FindNext(rngfound)
Loop While Not rngfound Is Nothing And rngfound.Address < firstaddress
End If
Next
End Sub


--


Gary


"junkman" wrote in message
...
I have one column that has list of phone numbers with area code. I
need to make new column for which state. I would like to make a small
macro program to check the area code and put two letters state on new
column. How do we do that. I was thinking to use loop for read each
row and use if area code = (301) then row 1 = CA elseif area code =
(302) then row 1 = UT.... I don't know where to start..

area code
 

Hi Junkman

Why don't you use vlookup?

on a new sheet make following matrix:
Column A: AreaCodes
Column B: State

then on your original sheet put following formula in your first row:

=vlookup(left(B1,4),matrix!$A$1:$B$50,2,false)

you have to adjust following data:
left(B1,4) should return the code how you entered it in the matrix in
column A
matrix!$A$1:$B$50 should be the area of the matrix

hth otherwise ask :)

Carlo

On Dec 19, 12:43 pm, junkman wrote:
I have one column that has list of phone numbers with area code. I
need to make new column for which state. I would like to make a small
macro program to check the area code and put two letters state on new
column. How do we do that. I was thinking to use loop for read each
row and use if area code = (301) then row 1 = CA elseif area code =
(302) then row 1 = UT.... I don't know where to start..