Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.
Course Of The Month
5 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Use Excel Macro to append text to cell
Posted on 2004-09-30
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
Active today
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
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
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
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
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
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
Active today
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses
Course of the Month5 days, 8 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.