Adding a symbol or test to an existing cell

  • Thread starter Thread starter JBM
  • Start date Start date
J

JBM

How can I take the contents of a cell, which is test, and
replace it with the same text, only with a minus sign in
front of it. Inother words, change "abc" to "-abc"? I
would prefer it be in the form of a macro so it would
simply be a cntl+ any character. I'd really like to be
able to perform the command on a group of cells.

Thanks
 
One way:

Public Sub PrefixMinusSign()
Dim rCell As Range
For Each rCell In Selection
With rCell
If Not IsEmpty(.Value) Then _
.Value = "-" & .Value
End With
Next rCell
End Sub

Note: this will ignore blank cells in the selection.
 
Thanks. This looks very interesting, but over my head.
Can I create a macro from this? If so, could you explain?
And would I simply highlight a cell or range of cells and
then initiate the command? Thanks again.
 
JBM

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text to Add")
If whichside = 1 Then
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Else
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next
End If
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Gord Dibben Excel MVP
 
Hi JE,

I've taken the code you've posted here, revised a bit and
applied it to a Workbook I'm working on. The code, with
revisions, works fine but I'd like to know if there's a
way to stop the loop (if it's truly a loop) when it hits a
cell with data (dates in this case) already in it. Or, at
least not add or change the cells within the defined range
that already contain dates. Right now it copies today's
date in the entire defined Range.

Sub BillingDateEntry()
Sheets("Data").Select
ActiveSheet.Unprotect
Range("DV4").Select
Selection.Copy<===this copies a Today() cell
Range("BD4:BD25").Select
Dim rCell As Range
For Each rCell In Selection
With rCell
If IsEmpty(.Value) Then _
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, Transpose:=False
End With
Next rCell
Application.CutCopyMode = False
Range("BD4").Select
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Sheet2").Select
End Sub



Would appreciate you looking at this and commenting.

Thanks in Advance,

Don
 
One way:

This will put today's date in any blank cell in the range BD4:BD25:

Public Sub BillingDateEntry()
Dim rCell As Range
Dim rSub As Range
With Sheets("Data")
On Error Resume Next 'in case no blanks
Set rSub = .Range("BD4:BD25").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rSub Is Nothing Then
.Unprotect
For Each rCell In rSub
rCell.Value = Date
Next rCell
.Protect
End If
End With
End Sub
 
Hi JE,

Thank you very much for your reply. It wouldn't work right
out of the box but with a couple of lines added it works
great. The macro starts from "Sheet2" and "Data" is
protected. I also wanted it to end up back on "Sheet2". I
think the protection is what made it fail, but it's
working great now.

Public Sub BillingDateEntry()
Sheets("Data").Select
ActiveSheet.Unprotect
Dim rCell As Range
Dim rSub As Range
With Sheets("Data")
On Error Resume Next 'in case no blanks
Set rSub = .Range("BD4:BD25").SpecialCells
(xlCellTypeBlanks)
On Error GoTo 0
If Not rSub Is Nothing Then
.Unprotect
For Each rCell In rSub
rCell.Value = Date
Next rCell
.Protect
End If
End With
Sheets("Sheet2").Select
End Sub

I noticed in the code you posted, you used only ".Protect"
to protect the sheet. I've been
using "ActiveSheet.Protect" and "ActiveSheet.Unprotect".
Why isn't "ActiveSheet.Protect" required at that point?

Thank you again for the help and time on this. You have a
great day now,

Don
 
No need to do any selections at all. Just move the .unprotect and
..protect statements:

Public Sub BillingDateEntry()
Dim rCell As Range
Dim rSub As Range
With Sheets("Data")
.Unprotect
On Error Resume Next 'in case no blanks
Set rSub = .Range("BD4:BD25").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rSub Is Nothing Then
For Each rCell In rSub
rCell.Value = Date
Next rCell
End If
.Protect
End With
End Sub

Selections are almost never necessary - working with the range objects
directly makes your code smaller, faster and IMO easier to maintain.
 
Back
Top