What am I doing wrong?

M

MagnaForce

New Member
Joined
Jul 1, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Getting a mismatch error

VBA Code: 
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
I have added to your macro what you were missing but what I don't understand is: Column "C" which contains both County and City Names Is this a typo or does the cell contain both ? or is it County or City Names ? Elsewise your macro needs to check 'inside' the cells for the City; as your created the macro it's supposed that the contents of a cell is exactly as the name in array City.
VBA Code: 
Option Explicit
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
    Dim x          As Long                        '<- added
    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")
    Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    For n = Lastrow To 1 Step -1                  'finds the last row and works its way up
        For x = LBound(City) To UBound(City)      '<- added (read every name in array)
            If Range("C" & n).Value = City(x) Then '<- changed (compare cell to name)
                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 x                                    '<- added
    Next n
End Sub
 
Last edited:
Upvote 0
try using Match function to check the value exists in your array.

Rich (BB code): 
For n = Lastrow To 1 Step -1 'finds the last row and works its way up
        
          If Not IsError(Application.Match(Range("C" & n).Value, City, 0)) Then
            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

Dave
 
Upvote 0
Solution
rollis13 said:
I have added to your macro what you were missing but what I don't understand is: Column "C" which contains both County and City Names Is this a typo or does the cell contain both ? or is it County or City Names ? Elsewise your macro needs to check 'inside' the cells for the City; as your created the macro it's supposed that the contents of a cell is exactly as the name in array City.
VBA Code: 
Option Explicit
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
    Dim x          As Long                        '<- added
    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")
    Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    For n = Lastrow To 1 Step -1                  'finds the last row and works its way up
        For x = LBound(City) To UBound(City)      '<- added (read every name in array)
            If Range("C" & n).Value = City(x) Then '<- changed (compare cell to name)
                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 x                                    '<- added
    Next n
End Sub
Click to expand...
This works, Thank you for the quick reply. Going to do some more research into LBound and Ubound to figure out what it does. This is the only way to learn.
 
Upvote 0
dmt32 said:
try using Match function to check the value exists in your array.

Rich (BB code): 
For n = Lastrow To 1 Step -1 'finds the last row and works its way up
       
          If Not IsError(Application.Match(Range("C" & n).Value, City, 0)) Then
            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

Dave
Click to expand...
This worked as well, less lines and faster. I ran both options and Rollis13' way ran in 0.445 Seconds and dmt32's way ran in 0.224 seconds
 
Upvote 0
Well, to make it simple, LBound and Ubound would be First and Last index of element of data in array (starts from 0).
Yes, Match is much faster that looping through the entire array. That's why there is the function ;).
You didn't answer to my question on the contents of a cell.
 
Upvote 0
rollis13 said:
Well, to make it simple, LBound and Ubound would be First and Last index of element of data in array (starts from 0).
Yes, Match is much faster that looping through the entire array. That's why there is the function ;).
You didn't answer to my question on the contents of a cell.
Click to expand...
To answer the question about the data. I have to download data from an online database. When the data gets here, the Cites and Counties are listed as a number in one column. I had to have a different array set up to change the numbers to County or City names first. I am going to use this data to populate a PDF form so I needed to separate the cities from counties because the PDF form has a box for City and One for County. I hope this made since. Thanks for your help, I am learning VBA and every little bit helps.

Usually when I look up code, the examples given either don't make since or they use examples that have nothing to do with what I am trying to do. That makes it hard for me to learn.
I was under the impression that LBound and UBound only had to be used in an array that is like this: Arr1("001", "002", ...) Arr2("Accomack", "Albemarle",...). I thought that UBound would be the upper array to read the numbers and LBound was the lower array to change the number to the text in this array.

Because I was using only one array with text I didn't think I needed that line of code you added. This website is a great source of knowledge, Thanks for being a part of it. Best Wishes.
 
Upvote 0
This is the code that I have to change the County Number into the County name, it works but does not have the
VBA Code: 
LBound

Code: 
Sub County()
    
    Dim CountyNum As Variant
    Dim CountyName As Variant
    Dim k As Long
    
CountyNum = Array("000", "001", "002", "003", "004", "005", "006", "007", "008", "009", "010", "011", "012", _
    "013", "014", "015", "016", "017", "018", "019", "020", "021", "022", "023", "024", "025", "026", "027", "028", _
    "029", "030", "031", "032", "033", "034", "035", "036", "037", "038", "039", "040", "041", "042", "043", "044", _
    "045", "046", "047", "048", "049", "050", "051", "052", "053", "054", "055", "056", "057", "058", "059", "060", _
    "061", "062", "063", "064", "065", "066", "067", "068", "069", "070", "071", "072", "073", "074", "075", "076", _
    "077", "078", "079", "080", "081", "082", "083", "084", "085", "086", "087", "088", "089", "090", "091", "092", _
    "093", "094", "095", "096", "097", "098", "099", "100", "101", "102", "103", "104", "105", "106", "107", "108", _
    "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", _
    "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "136", "137", "138", "139", "140", "141", _
    "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", _
    "158", "159", "162", "166", "176", "186", "204", "216", "228", "235", "252", "253", "266", "275", "279", "295", _
    "300", "301", "329", "330")

CountyName = Array("Arlington", "Accomack", "Albemarle", "Alleghany", "Amelia", "Amherst", "Appomattox", "Augusta", _
    "Bath", "Bedford", "Bland", "Botetourt", "Brunswick", "Buchanan", "Buckingham", "Campbell", "Caroline", "Carroll", _
    "Charles City", "Charlotte", "Chesterfield", "Clarke", "Craig", "Culpeper", "Cumberland", "Dickenson", "Dinwiddie", _
    "Elizabeth City", "Essex", "Fairfax", "Fauquier", "Floyd", "Fluvanna", "Franklin", "Frederick", "Giles", "Gloucester", _
    "Goochland", "Grayson", "Greene", "Greensville", "Halifax", "Hanover", "Henrico", "Henry", "Highland", "Isle of Wight", _
    "James City", "King George", "King & Queen", "King William", "Lancaster", "Lee", "Loudoun", "Louisa", "Lunenburg", _
    "Madison", "Mathews", "Mecklenburg", "Middlesex", "Montgomery", "Nansemond", "Nelson", "New Kent", "Norfolk", "Northampton", _
    "Northumberland", "Nottoway", "Orange", "Page", "Patrick", "Pittsylvania", "Powhatan", "Prince Edward", "Prince Gorge", _
    "Princess Anne", "Prince William", "Pulaski", "Rappahannock", "Richmond", "Roanoke", "Rockbridge", "Rockingham", "Russell", _
    "Scott", "Shenandoah", "Smyth", "Southampton", "Spotsylvania", "Stafford", "Surry", "Sussex", "Tazewell", "Warren", "Warwick", _
    "Washington", "Westmoreland", "Wise", "Wythe", "York", "Alexandria", "Big Stone Gap", "Bristol", "Buena Vista", _
    "Charlottesville", "Clifton Forge", "Colonial Heights", "Covington", "Danville", "Emporia", "Falls Church", "Fredericksburg", _
    "Front Royal", "Galax", "Hampton", "Harrisonburg", "Hopewell", "Lexington", "Lynchburg", "Marion", "Martinsville", "Newport News", _
    "Norfolk", "Petersburg", "Portsmouth", "Pulaski", "Radford", "Richmond", "Roanoke", "Salem", "South Boston", "Chesapeake", _
    "Staunton", "Suffolk", "Virginia Beach", "Waynesboro", "Williamsburg", "Winchester", "Wytheville", "Abingdon", "Bedford", _
    "Blackstone", "Bluefield", "Farmville", "Franklin", "Norton", "Poquoson", "Richlands", "Vinton", "Blacksburg", "Fairfax", _
    "Manassas Park", "Vienna", "Christiansburg", "Manassas", "Warrenton", "Rocky Mount", "Tazewell", "Luray", "Altavista", _
    "Ashland", "Bridgewater", "Chase City", "Culpeper", "Elkton", "Grottoes", "Herndon", "Lebanon", "Leesburg", "Narrows", _
    "Orange", "Pearisburg", "Saltville", "Smithfield", "South Hill", "Wise", "Woodstock")

    If UBound(CountyNum) <> UBound(CountyName) Then
        MsgBox "Invalid arrays"
        Exit Sub
    Else

        With ActiveSheet.UsedRange.Columns("B")

            For k = 0 To UBound(CountyNum)
                .Replace What:=CountyNum(k), Replacement:=CountyName(k), LookAt:=xlPart, MatchCase:=False
            Next

        End With
    End If
    
End Sub

Now I am further confused!
 
Upvote 0
Try this code
VBA Code: 
Sub City()
    Dim Lastrow As Long, n As Long
    Dim City As Variant
    Dim k As Boolean
    On Error Resume Next

    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
        k = False
        k = WorksheetFunction.IsNumber(WorksheetFunction.Match(Range("C" & n).Value, City, 0))
  
          If k = True 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
 
Upvote 0
Now it's me very confused. Do you mind showing how your data is when it "gets here"; maybe a couple of rows with XL2BB.
By the way, are you sure that what you need isn't just a couple of formulas ? with LEFT or RIGHT or MID string.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VLOOKUP Formula
Replies
3
Views
424
hajiali
H

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top