Help from people that really know (well more than me)

Duffer2468

New Member
Joined
Sep 8, 2014
Messages
8
I would like to have a VBA (or macro if no able) to do a searchof a column and either replace or create a new column with the modifications.
Let's use column A for the data to work with.
I need to have it look at column A and if it see an A, B or C change that valueto a 2
If it see an D, F, or G then change value to a 3
EG
if it see in column A
0019E1E21712
It changes in column A or places in column B

9001931321712
As a bonus it would be great to add 7 characters to the front ofthe string and 2 characters to the back of the string

Thank you
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
To the front I would like to add
**73639
and to the end
##

I did up a messy macro for the replacement but it is ugly
thanks for your response and reading my query
 
Upvote 0
Code:
Sub replaceNumbers()

    Dim newVal As String


    For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        newVal = "**73639"
        For y = 1 To Len(Cells(x, 1))
        
            Select Case Mid(Cells(x, 1), y, 1)
                Case "A", "B", "C"
                    newVal = newVal & "2"
                Case "D", "E", "F", "G"
                    newVal = newVal & "3"
                Case Else
                    newVal = newVal & Mid(Cells(x, 1), y, 1)
            End Select
        Next y
        newVal = newVal & "##"
        Cells(x, 1).value = newVal
    Next x
End Sub
 
Upvote 0
While it looks good it does not do anything?
If I step into it I get stopped at the first line
Sub replaceNumbers()
Not sure if this would affect this code but I am using Excel 2010 (I ran into issue with modules and codes I had from excel 97 and 2003 not working in this version.
Regards
 
Upvote 0
It just does nothing and when I go into the develpoer and I run the code it stops on the first line
Sub replaceNumbers()
I did place in the worksheet?
Have I made an error.
I love the way the code looks you would cry if you saw my macros (Frankenstein style)
 
Upvote 0
If it stops, it must be throwing an error. F8 will go line by line so you'll have to spam F8. When you press F5, does it give you any kind of error? Are you in break mode?
 
Upvote 0

Forum statistics

Threads
1,223,393
Messages
6,171,838
Members
452,427
Latest member
samk379

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