What am I doing wrong?

[MagnaForce]

Getting a mismatch error

Sub City()

    'This Sub is going to look at Column "C" which contains both County and City Names, if the cell
    'contains a City name then it will move it to Column "B"
    
    Dim Lastrow As Long, n As Long
    Dim City As Variant
    
    Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row

    City = Array("Abingdon", "Alexandria", "Altavista", "Ashland", "Bedford", "Big Stone Gap", _
    "Blacksburg", "Blackstone", "Bluefield", "Bridgewater", "Bristol", "Buena Vista", "Charlottesville", _
    "Chase City", "Chesapeake", "Christiansburg", "Clifton Forge", "Colonial Heights", "Covington", _
    "Culpeper", "Danville", "Elkton", "Emporia", "Fairfax", "Falls Church", "Farmville", "Franklin", _
    "Fredericksburg", "Front Royal", "Galax", "Grottoes", "Hampton", "Harrisonburg", "Herndon", "Hopewell", _
    "Lebanon", "Leesburg", "Lexington", "Luray", "Lynchburg", "Manassas", "Manassas Park", "Marion", _
    "Martinsville", "Narrows", "Newport News", "Norfolk", "Norton", "Orange", "Pearisburg", "Petersburg", _
    "Poquoson", "Portsmouth", "Pulaski", "Radford", "Richlands", "Richmond", "Roanoke", "Rocky Mount", "Salem", _
    "Saltville", "Smithfield", "South Boston", "South Hill", "Staunton", "Suffolk", "Tazewell", "Vienna", _
    "Vinton", "Virginia Beach", "Warrenton", "Waynesboro", "Williamsburg", "Winchester", "Wise", "Woodstock", "Wytheville")

        For n = Lastrow To 1 Step -1 'finds the last row and works its way up
          If Range("C" & n).Value = City Then '<getting a type mismatch on this line
            Range("B" & n).Value = Range("C" & n).Value 'Copies the contents from column "C" to "B"
            Range("C" & n).Value = "" 'Deletes contents in column "C" after it was copied
          End If
        Next
        
End Sub

 

[MagnaForce]

Here is a very small part of the Data that I get from the Database. Column "L" contains the County or City numbers. To make this usable for me I have to delete a bunch of columns of the information that I do not need. The the Sub for County() is called from my code to change the numbers into the corresponding County Name or City Name. Then I make several Sheets from the data. One of the sheets is for me to load into google maps to show me where the structures are. I did not include all of the columns in this example, but it does have the coordinates for the structures. Another sheet is added to build a report document to use in the field to enter data about the structure. and a third sheet is used to build a different form to do the actual inspection of the structure.

Step 1 - Remove all columns I do not need on any of the three sheets from the BIG Dataset that I get.
Step 2 - Create sheet for map and add all data from first sheet and then remove the columns not needed on the Map sheet, and change column names and a bunch of other stuff.
Step 3 - Build the sheet for inputting data into a PDF form, so I have to change a bunch headings, and all kinds of other stuff to talk to the PDF form, its a long process.

The PDF form I designed has the City in one box and if its a County it goes into a different box. this is why I have to separate the City from the County using the Sub City(). There are more counties than cities that I work in so the Counties will stay in column "C" and I just move the Cities over to a new column calle cities.

A formula like LEFT(), MID(), or RIGHT() would not work on the because as you can see it originally comes in as just a City Number or County Number.

Signals Asset Table.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
54	{6542F2EB-DB4C-465F-A234-949291930665}	68	{6542F2EB-DB4C-465F-A234-949291930665}	{4B4280A5-4F6C-450C-A6FA-F431F46B28F1}	0010062	TSP0014049	001016	10023B	001-0013-13	Active	Hampton Roads	001	N
55	{89A8F267-11D3-4622-A919-99B7F2B1E208}	70	{89A8F267-11D3-4622-A919-99B7F2B1E208}	{15308E01-7AA8-404F-A7D3-E8458751646F}	0010064	TSP0014057	001018	10024B	001-0013-15	Active	Hampton Roads	001	S
56	{2A805424-5F9F-4842-87F8-5AB5387769B9}	71	{2A805424-5F9F-4842-87F8-5AB5387769B9}		0010065	TSP0014064	001021	10025A		Active	Hampton Roads	001	W
57	{1E41BA38-6617-4CEE-8100-81CBC28EE1A3}	72	{1E41BA38-6617-4CEE-8100-81CBC28EE1A3}		0010066	TSP0014063		10025B		Active	Hampton Roads	001	E
58	{8045F611-3681-4517-8A97-42A2CE56BAD7}	74	{8045F611-3681-4517-8A97-42A2CE56BAD7}	{3268B084-73C1-45CE-A360-D742AAFF2E39}	0010068	TSP0014045	001014	010014C	001-0143-01	Active	Hampton Roads	001	N
59	{71B33B5E-C938-4B46-8043-A9CBB2BB3A49}	5022	{71B33B5E-C938-4B46-8043-A9CBB2BB3A49}		0400002	TSP1094006	081001	0400001B		Active	Hampton Roads	109	E
60	{6DF30A22-4ED7-430F-BF2A-D8B3E486F1B7}	5024	{6DF30A22-4ED7-430F-BF2A-D8B3E486F1B7}		0400004	TSP1094008	081001	0400001D		Active	Hampton Roads	109	W
61	{9D00A096-895E-4719-8008-4279C2F28E17}	5025	{9D00A096-895E-4719-8008-4279C2F28E17}		0400005	TSP1094001	081002	0400002A		Active	Hampton Roads	109	E
62	{2AEFE809-DDF1-452C-A5E7-9E16DEC61966}	5026	{2AEFE809-DDF1-452C-A5E7-9E16DEC61966}		0400006	TSP1094002	081002	0400002B		Active	Hampton Roads	109	W
63	{D61410C0-FC62-4E1E-BF93-C496171573CE}	5027	{D61410C0-FC62-4E1E-BF93-C496171573CE}		0400007	TSP1094003	081002	0400002C		Active	Hampton Roads	109	W
64	{80076D3A-A0BB-41EC-A5BC-BF222F191B06}	5028	{80076D3A-A0BB-41EC-A5BC-BF222F191B06}		0400008	TSP1094004	081002	0400002D		Active	Hampton Roads	109	E
65	{C1DB7C08-4DF8-47DD-973E-541FFA4A9A29}	5031	{C1DB7C08-4DF8-47DD-973E-541FFA4A9A29}	{5B5EA7E7-F532-449C-8D35-A46A0C958388}	0400011	TSP0404003		0400004A		Active	Hampton Roads	040	S
66	{28DB0990-A1F6-4AA3-A8AD-9D43945AA3C7}	5032	{28DB0990-A1F6-4AA3-A8AD-9D43945AA3C7}	{5B5EA7E7-F532-449C-8D35-A46A0C958388}	0400012	TSP0404004		0400004B		Active	Hampton Roads	040	N
67	{E3B34FB7-5E79-491C-AABE-1EE04FDB313E}	5830	{E3B34FB7-5E79-491C-AABE-1EE04FDB313E}		0460004	TSP0464033	093010	460002A		Active	Hampton Roads	046	W
68	{55C01C76-A061-4058-9E29-0F166BBB0123}	5831	{55C01C76-A061-4058-9E29-0F166BBB0123}		0460005	TSP0464034	093010	460002B		Active	Hampton Roads	046	E
69	{2FC78603-8164-4099-9904-42F5DE5B0880}	5835	{2FC78603-8164-4099-9904-42F5DE5B0880}	{3FC34C31-4FC3-4321-97DB-C31B0EE3D617}	0460009	TSP0464031	093011	460003B	093-0010-03	Active	Hampton Roads	046	W
70	{22593F0A-FFCA-44A2-8185-9D8FC66BA6C6}	5836	{22593F0A-FFCA-44A2-8185-9D8FC66BA6C6}	{3FC34C31-4FC3-4321-97DB-C31B0EE3D617}	0460010	TSP0464032	093011	460003C	093-0010-03	Active	Hampton Roads	046	W
71	{D93EFD97-B94C-41D8-A559-AD67E9791E9F}	5837	{D93EFD97-B94C-41D8-A559-AD67E9791E9F}	{DF90F21F-36C3-4193-B2D9-A707C5576BE0}	0460011	TSP3004008	093012	460004A	093-0010-04	Active	Hampton Roads	300	E
72	{0F5111B7-A78C-4BB6-BAB3-1775187D213E}	5838	{0F5111B7-A78C-4BB6-BAB3-1775187D213E}	{DF90F21F-36C3-4193-B2D9-A707C5576BE0}	0460012	TSP3004007	093012	460004B	093-0010-04	Active	Hampton Roads	300	W
73	{BA6F343F-67DA-4E44-BA23-B72455B282AD}	5841	{BA6F343F-67DA-4E44-BA23-B72455B282AD}	{3FF9D791-284C-4734-972F-9CE6D8E6DB47}	0460015	TSP3004005	093013	460005A	181-0010-01	Active	Hampton Roads	300	W
74	{AA5AD601-2507-4185-A518-4A8ADA0C3DD5}	5842	{AA5AD601-2507-4185-A518-4A8ADA0C3DD5}	{3FF9D791-284C-4734-972F-9CE6D8E6DB47}	0460016	TSP3004006	093013	460005B	181-0010-01	Active	Hampton Roads	300	E
75	{1E0CA8AB-259C-4B71-91CF-673FE78571E3}	5843	{1E0CA8AB-259C-4B71-91CF-673FE78571E3}	{5733181B-3188-4DFE-B3D0-0F4A4869C305}	0460017	TSP3004001	093017	460006A	093-0010-09	Active	Hampton Roads	300	E
76	{35AC3045-8B69-4F55-8077-DC801FD8DF47}	5844	{35AC3045-8B69-4F55-8077-DC801FD8DF47}	{5733181B-3188-4DFE-B3D0-0F4A4869C305}	0460018	TSP3004002	093017	460006B	093-0010-09	Active	Hampton Roads	300	E
77	{B9F0B1DA-7A83-4293-B09B-CC7D1636B095}	5845	{B9F0B1DA-7A83-4293-B09B-CC7D1636B095}	{5733181B-3188-4DFE-B3D0-0F4A4869C305}	0460019	TSP3004003	093017	460006C	093-0010-09	Active	Hampton Roads	300	W
78	{B43B3946-4C9B-4F00-85B2-7128F5831F7D}	5847	{B43B3946-4C9B-4F00-85B2-7128F5831F7D}		0460021	TSP3004011	093014	460007A		Active	Hampton Roads	300	E
79	{B3CE25E9-ED8D-4C27-BECB-8C5F864BE7F9}	5848	{B3CE25E9-ED8D-4C27-BECB-8C5F864BE7F9}		0460022	TSP3004012	093014	460007B		Active	Hampton Roads	300	W
80	{99E3E50D-8EC9-4ADC-9494-CF203D7F42EA}	5849	{99E3E50D-8EC9-4ADC-9494-CF203D7F42EA}	{C3C06CD5-90CA-439C-A149-CADF8F4D35AB}	0460023	TSP0464013	093024	460008A	093-0017-02	Active	Hampton Roads	046	S
81	{216149FE-2F87-4132-98E9-81E01D0D8CC8}	5850	{216149FE-2F87-4132-98E9-81E01D0D8CC8}	{C3C06CD5-90CA-439C-A149-CADF8F4D35AB}	0460024	TSP0464014	093024	460008B	093-0017-02	Active	Hampton Roads	046	S
82	{9B80323A-56F7-40D3-817F-47CC1D1A77FF}	5851	{9B80323A-56F7-40D3-817F-47CC1D1A77FF}	{C3C06CD5-90CA-439C-A149-CADF8F4D35AB}	0460025	TSP0464015	093024	460008C	093-0017-02	Active	Hampton Roads	046	N
83	{0EBDFDB0-600F-4A34-BAF6-1D29E1AF0A04}	5852	{0EBDFDB0-600F-4A34-BAF6-1D29E1AF0A04}	{C3C06CD5-90CA-439C-A149-CADF8F4D35AB}	0460026	TSP0464016	093024	460008D	093-0017-02	Active	Hampton Roads	046	N
84	{8D1F7342-DAB6-4421-BC5D-ED57967F731E}	5853	{8D1F7342-DAB6-4421-BC5D-ED57967F731E}	{85EFACA7-4768-40A3-905A-8084C76D7C26}	0460027	TSP0464009	093023	460009A	093-0017-03	Active	Hampton Roads	046	S
85	{2DD41EF2-BE9E-4791-9CBC-DF01B1B009B6}	5854	{2DD41EF2-BE9E-4791-9CBC-DF01B1B009B6}	{85EFACA7-4768-40A3-905A-8084C76D7C26}	0460028	TSP0464010	093023	460009B	093-0017-03	Active	Hampton Roads	046	S
86	{413096DB-FB03-4FB4-BBE8-77FE531B7A19}	5855	{413096DB-FB03-4FB4-BBE8-77FE531B7A19}	{85EFACA7-4768-40A3-905A-8084C76D7C26}	0460029	TSP0464011	093023	460009C	093-0017-03	Active	Hampton Roads	046	N
87	{32954F97-FBC3-4C7E-B785-070CDD3300E8}	5856	{32954F97-FBC3-4C7E-B785-070CDD3300E8}	{85EFACA7-4768-40A3-905A-8084C76D7C26}	0460030	TSP0464012	093023	460009D	093-0017-03	Active	Hampton Roads	046	N
88	{8DA3668E-585A-4274-BBBB-B33C73AFAEBB}	5860	{8DA3668E-585A-4274-BBBB-B33C73AFAEBB}	{AF018A34-4062-4117-A372-C8A42D03D9EF}	0460034	TSP0464001	150	460011A	093-0017-06	Active	Hampton Roads	046	S
Content

 

[rollis13]

I'm still confused. This is what I understood (using your words in post #11):
- Column "L" contains the County or City numbers.
- Sub County() changes the numbers into the corresponding County Name or City Name.
- I have to separate the City from the County using the Sub City() so the Counties will stay in column "L" and moves the Cities over to a new column called Cities.
Is this what is supposed to happen ?

 

[MagnaForce]

I'm still confused. This is what I understood (using your words in post #11):
- Column "L" contains the County or City numbers.
- Sub County() changes the numbers into the corresponding County Name or City Name.
- I have to separate the City from the County using the Sub City() so the Counties will stay in column "L" and moves the Cities over to a new column called Cities.
Is this what is supposed to happen ?

Yes, and that is exactly what is happening now that I am using the code - If Not IsError(Application.Match(Range("C" & n).Value, City, 0)) Then - or the code you provided it does the same thing. This reads the names of the cities from the array and if it matches a city name then it moves the city name over to the cities column. and just leaves the County names in the county column.

Here is the final result. Of course this is just a very small part of the final table. Maybe the confusion comes from the first array is just named County but all of the numbers are City numbers and County numbers combined in the same column. I guess I could have named the first array that converts the numbers into City and County Names to CityCountyNum and CityCountyName because they are really all combined in the table as just numbers. But I needed to move just the City names from that column to the City Column for use in something else.

Signals Asset Table.xlsx
	A	B	C	D	E	F
1	Structure_Number	City	County	Location_Description	Route_ID	Roadway_Type
2	TSP0994170		York	Structure is located on F-137 (E. Rochambeau Rd) WBL, at intersection with Cedar Valley Shopping Center.	SC0F137WB	Primary
3	TSP0994171		York	Structure is located on F-137 (E. Rochambeau Rd) WBL, at intersection with Cedar Valley Shopping Center.	SC0F137WB	Primary
4	TSP0994172		York	Structure is located on SC-603 (Mooretown Rd) EBL, at intersection with SC-646 (Lightfoot Rd).	SC00603EB	Secondary
5	TSP0994173		York	Structure is located on SC-603 (Mooretown Rd) WBL, at intersection with SC-646 (Lightfoot Rd).	SC00603EB	Secondary
6	TSP0994174		York	Structure is located on SC-603 (Mooretown Rd) WBL, at intersection with SC-646 (Lightfoot Rd).	SC00603WB	Secondary
7	TSP0994175		York	Structure is located on SC-603 (Mooretown Rd) EBL, at intersection with SC-646 (Lightfoot Rd).	SC00603WB	Secondary
8	TSP0994176		York	Structure is located on US-60 WBL, at intersection with SC-614 (Centerville Road).	US00060WB	Primary
9	TSP0994177		York	Structure is located on US-60 EBL, at intersection with SC-614 (Centerville Road).	US00060EB	Primary
10	TSP0994178		York	Structure is located on US-60 WBL, at intersection with SC-614 (Centerville Road).	US00060WB	Primary
11	TSP0994179		York	Structure is located on US-60 EBL, at intersection with Outlet Mall.	US00060EB	Primary
12	TSP0994180		York	Structure is located on US-60 WBL, at intersection with SC-646 (Lightfoot Road).	US00060WB	Primary
13	TSP1094001	Emporia		Structure is located on US-58 (Atlantic St) EBL, at intersection with SC-619 (Wiggins Rd).	US00058EB	Primary
14	TSP1094002	Emporia		Structure is located on US-58 (Atlantic St) WBL, at intersection with SC-619 (Purdy Rd).	US00058WB	Primary
15	TSP1094003	Emporia		Structure is located on US-58 (Atlantic St) WBL, at intersection with SC-619 (Purdy Rd).	US00058WB	Primary
16	TSP1094004	Emporia		Structure is located on US-58 (Atlantic St) EBL, at intersection with SC-619 (Wiggins Rd).	US00058EB	Primary
17	TSP1094005	Emporia		Structure is located on US-58 (Atlantic St) EBL, at intersection with US-58 Bus.	US00058EB	Primary
18	TSP1094006	Emporia		Structure is located on US-58 (Atlantic St) EBL, at intersection with US-58 Bus.	US00058EB	Primary
19	TSP1094007	Emporia		Structure is located on US-58 (Atlantic St) WBL, at intersection with Market Dr.	US00058WB	Primary
20	TSP1094008	Emporia		Structure is located on US-58 (Atlantic St) WBL, at intersection with Market Dr.	US00058WB	Primary
21	TSP1244000	Newport News		Structure is located on VA-164 (Western Fwy) EBL, at intersection with APM Terminal Blvd.	VA00164EB	Primary
22	TSP3004001	Smithfield		Structure is located on VA-10 EBL, at intersection with W Main St.	VA00010EB	Primary
23	TSP3004002	Smithfield		Structure is located on VA-10 EBL, at intersection with W Main St.	VA00010EB	Primary
24	TSP3004003	Smithfield		Structure is located on VA-10 WBL, at intersection with US-258 Bus (Main St).	VA00010WB	Primary
25	TSP3004004	Smithfield		Structure is located on VA-10 WBL, at intersection with US-258 Bus (Main St).	VA00010WB	Primary
26	TSP3004005	Smithfield		Structure is located on VA-10 (Benns Church Blvd) WBL, at intersection with VA-10/US-258 Bus (Church St S).	VA00010WB	Primary
27	TSP3004006	Smithfield		Structure is located on VA-10 (Benns Church Blvd) EBL, at intersection with VA-10/US-258 Bus (Church St S).	VA00010EB	Primary
28	TSP3004007	Smithfield		Structure is located on VA-10 (Benns Chruch Blvd) WBL, at intersection with Canteberry Lane.	VA00010WB	Primary
29	TSP3004008	Smithfield		Structure is located on VA-10 (Benns Chruch Blvd) EBL, at intersection with Canteberry Lane.	VA00010EB	Interstate
30	TSP3004009	Smithfield		Structure is located on VA-10 (Church St) WBL, at intersection with W Main St.	VA00010WB	Primary
31	TSP3004010	Smithfield		Structure is located on VA-10 (Church St S) EBL, at intersection with Main St.	VA00010EB	Primary
32	TSP3004011	Smithfield		Structure is located on VA-10 (Benns Church Blvd) EBL, Bus, at intersection with Holt St.	BUS00010EB	Primary
33	TSP3004012	Smithfield		Structure is located on VA-10 (Benns Church Blvd) WBL, Bus, at intersection with SC-704 (Battery Park Rd).	BUS00010WB	Primary
Build_Field_Reports