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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
Euro5Author Commented:
THAT'S IT!!! THANK YOU!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.