edit the content of cells in column using vba

In sheet "Filtered_Data", I need a code to change the cells in column named "Street Address" - don't know what column that might be in.
For each cell from row 2 to End - don't know how many rows...
1. remove all numbers
2. keep only first 6 characters
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub Macro()
Dim s()
s = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
With Worksheets("Filtered_Data").Range("1:1")
    Set f = .Find("Street Address", LookIn:=xlValues)
    If Not f Is Nothing Then
        Set mCol = Range(Cells(2, f.Column), Cells(Rows.Count, f.Column).End(xlUp))
        For Each c In mCol
            v = c.Value
            For i = 0 To 9
                v = Replace(v, s(i), "")
            Next
            c.Value = Left(v, 6)
        Next
    End If
End With
End Sub

Open in new window

Regards
0
Euro5Author Commented:
Rgonzo1971,
The code does not appear to effect the cells at all.
I will upload the sample data.

The code is on Sub Make_Pivot()
'edit address

Can you help?
test-for-address.xlsb
0
Rgonzo1971Commented:
you changed the var just once not enough at line 9

ad = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
With Worksheets("Filtered_Data").Range("1:1")
    Set f = .Find("Street Address", LookIn:=xlValues)
    If Not f Is Nothing Then
        Set mCol = Range(Cells(2, f.Column), Cells(Rows.Count, f.Column).End(xlUp))
        For Each c In mCol
            v = c.Value
            For i = 0 To 9
                v = Replace(v, ad(i), "")
            Next
            c.Value = Trim(Left(v, 6))
        Next
    End If
End With

Open in new window

And add Trim at line 11
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Euro5Author Commented:
This was working great - THEN - it gave me an error.
#NAME? was in the cell and it error said it was a type mismatch.
Any ideas?
Sub Edit_Address()

Dim ad()

ad = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")


With Worksheets("Data").Range("1:1")
    Set f = .Find("Street Address", LookIn:=xlValues)
    If Not f Is Nothing Then
        Set mCol = Range(Cells(2, f.Column), Cells(Rows.Count, f.Column).End(xlUp))
        For Each c In mCol
            v = c.Value
            For i = 0 To 9
                v = Replace(v, ad(i), "")
            Next
            c.Value = Left(v, 6)
       Next
    End If
End With
End Sub

Open in new window

0
Anne TroyEast Coast ManagerCommented:
I did not get the error, but there was a space left in front of the text part, so I added the trim. If you could paste exactly the data you're working with, perhaps we can figure it out.

Sub Edit_Address()

Dim ad()

ad = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")


With Worksheets("Data").Range("1:1")
    Set f = .Find("Street Address", LookIn:=xlValues)
    If Not f Is Nothing Then
        Set mCol = Range(Cells(2, f.Column), Cells(Rows.Count, f.Column).End(xlUp))
        For Each c In mCol
            v = c.Value
            For i = 0 To 9
                v = Replace(v, ad(i), "")
            Next
            c.Value = Trim(Left(v, 6))
       Next
    End If
End With
End Sub

Open in new window

0
Euro5Author Commented:
Dreamboat,
I am attaching a sample with the code in it.

It does not work for me, can you test?
0
Euro5Author Commented:
I get an error when it gets to #NAME?
test-for-address.xlsm
0
Anne TroyEast Coast ManagerCommented:
I can't say I'm sure, but since no one else is responding.
        For Each c In mCol
            v = c.Value

Open in new window


Change c.Value to c.Text
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
THAT'S IT!!! THANK YOU!! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.