Can A Macro "Add" Content To A Cell Without Replacing The Original Content?

[Minitman]

Greetings,

I have a very long list of telephone numbers. Some of them have the
area code but most do not. I am trying to make a macro to add the
area code to the selected phone number. I was hoping to place this
macro on a command button (button already sitting there) but I can't
seem to get any macro to go into the selected cell, go to the far left
of the cell and then paste (512) there and then go down one cell.
All the macro recorder records is to replace the contents of the cell
with the resultant ten digit number.

Anyone have any ideas on how to paste the area code in front of the
existing 7 digit number?

TIA

-Minitman

 

[Bob Flanagan]

Minitman, the following will modify all the cells in a selection that
contain an entry:

Sub AddAreaCode()
Dim cell As Range
For Each cell In Selection
If Not IsEmpty(cell) Then
cell.Value = "512" & " " & cell.Value
End If
Next
End Sub

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Frank Kabel]

Hi
see your replies in Excel.misc
P.S.: please don't multipost

 

[Robin Hammond]

I don't think a standard paste is the solution. By the sounds of it, you
already have the cell selected that you want to edit. Your code should
probably include the following to add the 512 area code. Note the space
after the 512 forces conversion to a string datatype in the entered value.

activecell.value = "512 " & activecell.value
activecell.offset(1,0).select

However, it is much more efficient just to loop through the cells, something
like this.

Sub AddCodes()
Dim rngEdit As Range
Dim rngCell As Range
Dim strCode As String

'edit this to include the range of your numbers
Set rngEdit = Range("b1:b4")

For Each rngCell In rngEdit
'if they are all 512 codes, use this
'If Len(rngCell.Value) = 7 Then _
rngCell.Value = "512 " & rngCell.Value

'if you want to enter each area code manually, use this variant
'N.B. delete one of these variants
'this assumes the name of the contact is one column to the left
If Len(rngCell.Value) = 7 Then

strCode = CStr(InputBox("Please enter the area code for " & _
rngCell.Offset(0, -1).Value, "Enter code")) & " "
'you should put some error checking in here to make sure the code
'makes sense
rngCell.Value = strCode & rngCell.Value
End If
Next rngCell
End Sub


Robin Hammond
www.enhanceddatasystems.com