Formula in a template

[Guest]

I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?

(e-mail address removed)

 

[Gord Dibben]

How about event code to change the numbers as they are entered?

No formulas involved.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 And _
Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * -1
'.NumberFormat = "0;<0>"
Application.EnableEvents = True
End With
End Sub

Open a new workbook. Customize as you wish then place this event code into the
sheet.

Right-click on the the sheet tab and "View Code".

Copy/paste into that sheet module.

Now................File>Save As>File Type>Excel Template(*.xlt)

Give it a name and store it where you like.

I have no idea why your original template had no formulas unless you did not
save correctly.


Gord Dibben MS Excel MVP

 

[Guest]

Gord,

It works great but how do I limit the action to a single column?

 

[Dave Peterson]

One way:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

Application.EnableEvents = False
With Target
.Value = Abs(.Value) * -1
'.NumberFormat = "0;<0>"
End With
Application.EnableEvents = True
End Sub

I rearranged some stuff--for just arbitrary reasons.

 

[Gord Dibben]

Sorry about that Dennis.

Try this construct............change the ("A:A") to your choice.

OR ("A:A, C:C, E:E") for cols A, C and E

OR ("A1:B23") for a range

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit:
Application.EnableEvents = False
For Each rng In vRngInput
If IsNumeric(rng.Value) Then

With rng
.Value = .Value * -1
'.NumberFormat = "0;<0"

End With
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord