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. D

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


In article <(E-Mail Removed)>,
"StargateFanFromWork" <(E-Mail Removed)> 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. D

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. D

--

Dave Peterson

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. D




"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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. D
>
> --
>
> Dave Peterson

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.

StargateFanFromWork wrote:
>
> 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. D
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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. D
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

or maybe...

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

and just use two cells.

Dave Peterson wrote:
>
> 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.
>
> StargateFanFromWork wrote:
> >
> > 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. D
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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. D
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson