Addition results to show within same cell?

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

I don't know what to call what I need to do, so I'll describe the situation.

If I type in 100800 in A1, how can cell display that number + 199 so that,
although I typed in 100800, the cell displays:

100800 - 100999?

I usu. just do separate cells and do an easily addition formula, but I'm
finding that it would make life easier if I'm just dealing with one cell for
each range of numbers so that I'm just dealing with column A even though
there are 2 numbers.

Thanks. :oD
 
One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cdINCREMENT As Double = 199
With Range("A1")
If Not Intersect(Target, .Cells) Is Nothing Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = CStr(.Value) & " - " & _
CStr(.Value + cdINCREMENT)
Application.EnableEvents = True
End If
End If
End With
End Sub
 
I think you'd be much better served by using different cells for this kind of
thing.

But if you have to, you could use a worksheet event and have it do the work.

Rightclick on the worksheet tab that should have this behavior and select view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
'only one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only in column A
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

'no errors
If IsError(.Value) Then Exit Sub

'no empty cells
If IsEmpty(.Value) Then Exit Sub

'no formulas
If .HasFormula Then Exit Sub

'only numbers
If IsNumeric(.Value) = False Then Exit Sub

'do the work
Application.EnableEvents = False
.Value = .Value & " - " & .Value + 199

End With


ErrHandler:
Application.EnableEvents = True

End Sub

You'll have to change the range (I used column A). And you may want to use a
line like:

..Value = format(.Value, "000000") & " - " & format(.Value + 199, "000000")

If you have any leading 0's in those numbers that have to be kept.
 
I was hoping not to do this programmatically. This is too much for what the
sheet is needing to accomplish <g>.

Okay. Another approach ... is there a way to get Excel to copy the
information in any given 3 cells, i.e., 100800 - 100999 (each component in a
different cell), without translating the change in cells into tabs? What I
means is when I copy "100800 - 100999" and any other numbers below in the
sheet, the results come out as "100800[tab]-[tab]100999" rather than
"100800[space]-[space]100999".

Is there a way to get a copy of data to the clipboard as
"100800[space]-[space]100999" to begin with?? It really messes things up.
Yes, I can search and replace afterwards to gets spaces but I've been using
this sheet all afternoon to generate labels in Word and the fiddling gets
old, fast! <g>

Thanks. :oD
 
You could concatenate those three cells:

=a1&b1&c1
or
=text(a1,"000000")&b1&text(c1,"000000")

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.
I was hoping not to do this programmatically. This is too much for what the
sheet is needing to accomplish <g>.

Okay. Another approach ... is there a way to get Excel to copy the
information in any given 3 cells, i.e., 100800 - 100999 (each component in a
different cell), without translating the change in cells into tabs? What I
means is when I copy "100800 - 100999" and any other numbers below in the
sheet, the results come out as "100800[tab]-[tab]100999" rather than
"100800[space]-[space]100999".

Is there a way to get a copy of data to the clipboard as
"100800[space]-[space]100999" to begin with?? It really messes things up.
Yes, I can search and replace afterwards to gets spaces but I've been using
this sheet all afternoon to generate labels in Word and the fiddling gets
old, fast! <g>

Thanks. :oD

Dave Peterson said:
I think you'd be much better served by using different cells for this kind
of
thing.

But if you have to, you could use a worksheet event and have it do the
work.

Rightclick on the worksheet tab that should have this behavior and select
view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
'only one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only in column A
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

'no errors
If IsError(.Value) Then Exit Sub

'no empty cells
If IsEmpty(.Value) Then Exit Sub

'no formulas
If .HasFormula Then Exit Sub

'only numbers
If IsNumeric(.Value) = False Then Exit Sub

'do the work
Application.EnableEvents = False
.Value = .Value & " - " & .Value + 199

End With


ErrHandler:
Application.EnableEvents = True

End Sub

You'll have to change the range (I used column A). And you may want to
use a
line like:

.Value = format(.Value, "000000") & " - " & format(.Value + 199, "000000")

If you have any leading 0's in those numbers that have to be kept.
Click to expand...
Click to expand...
 
or maybe...

=a1&" - "&a1+199
or
=text(a1,"000000")&" - "&text(a1+199,"000000")

and just use two cells.

Dave said:
You could concatenate those three cells:

=a1&b1&c1
or
=text(a1,"000000")&b1&text(c1,"000000")

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.
I was hoping not to do this programmatically. This is too much for what the
sheet is needing to accomplish <g>.

Okay. Another approach ... is there a way to get Excel to copy the
information in any given 3 cells, i.e., 100800 - 100999 (each component in a
different cell), without translating the change in cells into tabs? What I
means is when I copy "100800 - 100999" and any other numbers below in the
sheet, the results come out as "100800[tab]-[tab]100999" rather than
"100800[space]-[space]100999".

Is there a way to get a copy of data to the clipboard as
"100800[space]-[space]100999" to begin with?? It really messes things up.
Yes, I can search and replace afterwards to gets spaces but I've been using
this sheet all afternoon to generate labels in Word and the fiddling gets
old, fast! <g>

Thanks. :oD

Dave Peterson said:
I think you'd be much better served by using different cells for this kind
of
thing.

But if you have to, you could use a worksheet event and have it do the
work.

Rightclick on the worksheet tab that should have this behavior and select
view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
'only one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only in column A
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

'no errors
If IsError(.Value) Then Exit Sub

'no empty cells
If IsEmpty(.Value) Then Exit Sub

'no formulas
If .HasFormula Then Exit Sub

'only numbers
If IsNumeric(.Value) = False Then Exit Sub

'do the work
Application.EnableEvents = False
.Value = .Value & " - " & .Value + 199

End With


ErrHandler:
Application.EnableEvents = True

End Sub

You'll have to change the range (I used column A). And you may want to
use a
line like:

.Value = format(.Value, "000000") & " - " & format(.Value + 199, "000000")

If you have any leading 0's in those numbers that have to be kept.

StargateFanFromWork wrote:

I don't know what to call what I need to do, so I'll describe the
situation.

If I type in 100800 in A1, how can cell display that number + 199 so
that,
although I typed in 100800, the cell displays:

100800 - 100999?

I usu. just do separate cells and do an easily addition formula, but I'm
finding that it would make life easier if I'm just dealing with one cell
for
each range of numbers so that I'm just dealing with column A even though
there are 2 numbers.

Thanks. :oD
Click to expand...
Click to expand...
Click to expand...
 
Back
Top