Compile Error

[Rodney Jorgensen]

I am getting this Compile Error:

Expected: Get or Let or Set

when I try and run this code. I had used this in a previous workbook and it worked. I had seen this compile error before when I tried it in a different workbook. Now I need to get it available in my new workbook. I have copied the module to my new workbook where I need this to work.

The code does 2 things, first, runs the RemovePunctuation Function (I believe this is where the Compile Error is comming from) and second replaces all the Abbreviations listed.

Sub Property()
    Application.ScreenUpdating = False
    Worksheets("Final").Select
    Dim c As Range, t As String
    For Each c In Range("BW4:BZ92")
        t = UCase(" " & RemovePunctuation(c.Text) & " ")
        t = Replace(t, " AVE ", " AVENUE ")
        t = Replace(t, " BLVD ", " BOULEVARD ")
        t = Replace(t, " BVD ", " BOULEVARD ")
        t = Replace(t, " CYN ", " CANYON ")
        t = Replace(t, " CTR ", " CENTER ")
        t = Replace(t, " CIR ", " CIRCLE ")
        t = Replace(t, " CT ", " COURT ")
        t = Replace(t, " DR ", " DRIVE ")
        t = Replace(t, " FWY ", " FREEWAY ")
        t = Replace(t, " HBR ", " HARBOR ")
        t = Replace(t, " HTS ", " HEIGHTS ")
        t = Replace(t, " HWY ", " HIGHWAY ")
        t = Replace(t, " JCT ", " JUNCTION ")
        t = Replace(t, " LN ", " LANE ")
        t = Replace(t, " MTN ", " MOUNTAIN ")
        t = Replace(t, " PKWY ", " PARKWAY ")
        t = Replace(t, " PL ", " PLACE ")
        t = Replace(t, " PLZ ", " PLAZA ")
        t = Replace(t, " RDG ", " RIDGE ")
        t = Replace(t, " RD ", " ROAD ")
        t = Replace(t, " RTE ", " ROUTE ")
        t = Replace(t, " ST ", " STREET ")
        t = Replace(t, " TRWY ", " THROUGHWAY ")
        t = Replace(t, " TL ", " TRAIL ")
        t = Replace(t, " TPKE ", " TURNPIKE ")
        t = Replace(t, " VLY ", " VALLEY ")
        t = Replace(t, " VLG ", " VILLAGE ")
        t = Replace(t, " APT ", " APARTMENT ")
        t = Replace(t, " APTS ", " APARTMENTS ")
        t = Replace(t, " BLDG ", " BUILDING ")
        t = Replace(t, " FLR ", " FLOOR ")
        t = Replace(t, " OFC ", " OFFICE ")
        t = Replace(t, " OF ", " OFFICE ")
        t = Replace(t, " APT ", " APARTMENT ")
        t = Replace(t, " STE ", " SUITE ")
        t = Replace(t, " N ", " NORTH ")
        t = Replace(t, " E ", " EAST ")
        t = Replace(t, " S ", " SOUTH ")
        t = Replace(t, " W ", " WEST ")
        t = Replace(t, " NE ", " NORTHEAST ")
        t = Replace(t, " SE ", " SOUTHEAST ")
        t = Replace(t, " SW ", " SOUTHWEST ")
        t = Replace(t, " NW ", " NORTHWEST ")
        t = Replace(t, " MHP ", " MOBILE HOME PARK ")
        t = Replace(t, " ", "")
        c = t
Next
    Application.ScreenUpdating = True
End Sub


Function RemovePunctuation(r As String) As String

    With CreateObject("vbscript.regexp")
        .Pattern = "[^A-Z0-9 ]"
        .IgnoreCase = True
        .Global = True
    RemovePunctuation = .Replace(r, "")
End With
End Function

Thanks for your help.

 

[Norie]

Rodney

I would suggest you don't call the sub Property.

Also why are you looping through all those cells?

Couldn't you use Find/Replace?

 

[Rodney Jorgensen]

How would I run the Sub Property then?

What I am doing is setting up a worksheet to compare 2 Data Bases againt each other. One of the Data Bases get input by hand and the other is auto uploaded through a Spreadsheet.

I am working with property names and street addresses. I am doing a If A=B then Ok Else Bad type thing here. The problem with doing just this is that there are way to many Bad responces, so I am working to cut down the unwanted responces by replacing any abbrieviations with the full words, removing all punctuation and spaces so the compare formula will comare character by character. It would be too time consuming to do a FInd/Replace for a spreadsheet that in this case has approximately 90 property names and addresses.

It has not been the quickest sub to run, but it does cut down the amount of unwanted responces quite a bit.

An example of 2 cells that have the same Property name that do not need to envoke a Bad Responce but will with the formula without this sub would be

Woodlake Apts and Woodlake Apartments

I do not need to spend time in the data analysis confirming that those 2 statements are the same. It does not matter if there are abbriviations used in either DB.

 

[Norie]

Rodney

There's no problem with the actual code - it's the name of the sub Property.

I don't know if it is a reserved word but I do know that VBA uses it for other purposes, normally in class modules I think.

 

[Rodney Jorgensen]

I changed the Sub name and it worked. Something so simple.

Thanks