Solved

Use Excel Macro to append text to cell

Posted on 2004-09-30
5
2,616 Views
Last Modified: 2008-02-26
I would like to use a Visual Basic Macro in Excel to take the selected cell and append "//" to the beginning of any text within.

Conversly, I would like to create a macro which removes "//" from any text within the selected cell.

I also would like to move the selection to the next cell down in the row.

Thanks!

-Matthew
0
Comment
Question by:nordee
  • 2
  • 2
5 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 50 total points
ID: 12190743
Hi Matthew,
Here is one macro that does both jobs--it removes the // if the cell contains it at the beginning, and adds it at the beginning otherwise. It then moves to the next cell in the same column.

Sub DoubleSlash()
Dim str As String
Dim cel As Range
Set cel = ActiveCell
If cel = "" Then Exit Sub
If Left(cel, 2) = "//" Then
    cel = Mid(cel, 3, Len(cel) - 2)
Else
    cel = "//" & cel
End If
cel.Offset(1, 0).Activate
End Sub

To install a sub or function, ALT + F11 to open the VBA Editor. Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

To run a sub, ALT + F8 to open the macro window. Select the macro, then click the Run button.

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Cheers!

Brad
0
 
LVL 6

Expert Comment

by:KingDumbNo
ID: 12191601
One _very_minor_ enhancement would be to change the line in Brad's code that reads
cel = Mid(cel, 3, Len(cel) - 2)
with this
cel = Mid(cel, 3)

It'll do the exact same thing but just less typing (and maybe less CPU, but hardly worth the effort to benchmark it).

Regards,
Emory
0
 

Author Comment

by:nordee
ID: 12193645
Excellent! That works great.

One quick question...is

Dim str as String

declaring a variable? If so, is it unused? It's the only thing in the code above I didn't understand.

Thanks.

-Matthew
0
 
LVL 81

Expert Comment

by:byundt
ID: 12194173
Matthew,
The str declaration is there because I forgot to take it out after the code was debugged.

I declare all my variables as a general principle, and start a project by declaring the ones I think I might need. Turns out that the str variable wasn't needed in the final version of the sub. You can safely delete that line of code.

The following code eliminates the extra declaration and also includes the simplified Mid function suggested by Emory:

Sub DoubleSlash()
Dim cel As Range
Set cel = ActiveCell
If cel = "" Then Exit Sub
If Left(cel, 2) = "//" Then
    cel = Mid(cel, 3)
Else
    cel = "//" & cel
End If
cel.Offset(1, 0).Activate
End Sub

Brad
0
 

Author Comment

by:nordee
ID: 12194251
Thank you for the clarification.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
WinxDVD Platinum 12 57
Migration from Netsuite to Quickbooks 1 37
suggestion for PC desktop software to send PayPal mass payments 2 33
Transferring files to iPhone 3 35
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now