check for alphabet in datalabel and then superscript that letter

Hi all,

I have a macro where the macro checks for the presence of datalabel and the
superscripts the 3rd character. so far this works fine, here is the code
below.

Sub Superscripting()

Dim x As Integer, y As Integer
Dim Ix As Integer
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
ActiveChart.SeriesCollection(1).DataLabels.Select
'ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select

For x = 1 To ActiveChart.SeriesCollection.Count
For y = 1 To ActiveChart.SeriesCollection(x).Points.Count
With ActiveChart.SeriesCollection(x)
If .Points(y).HasDataLabel Then
.Points(y).DataLabel.Characters(3, 1).Font.Superscript = True

End If

End With
Next y
Next x

End Sub

Now what i want to do is to determine if the datalabel has alphabet and
numbers, if only numbers then do nothing, but if it has alphabets as well
then superscript the alphabet part of the datalabel.

Hope some one could help me

Thanks a many.
Jyotsna

J,
See how this works for you.
Jim Cone
San Francisco, USA

'---------------------------------------
Sub Superscripting()
Dim blnOK As Boolean
Dim N As Long
Dim x As Long
Dim y As Long
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False

For x = 1 To ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(x)
For y = 1 To .Points.Count
If .Points(y).HasDataLabel Then
For N = 1 To Len(.Points(y).DataLabel.Text)
'If any alpha character then ok to format.
If Not Mid$(.Points(y).DataLabel.Text, N, 1) Like "#" Then
blnOK = True
Exit For
End If
Next 'N
'Make sure there are three characters.
If blnOK And Len(.Points(y).DataLabel.Text) > 2 Then _
.Points(y).DataLabel.Characters(3, 1).Font.Superscript = True
End If
blnOK = False
Next y
End With
Next x
End Sub
'---------------------------------


"Jyotsna" <[email protected]>
wrote in message
news:[email protected]...
Hi all,
I have a macro where the macro checks for the presence of datalabel and the
superscripts the 3rd character. so far this works fine, here is the code
below.

Sub Superscripting()
Dim x As Integer, y As Integer
Dim Ix As Integer
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
ActiveChart.SeriesCollection(1).DataLabels.Select
'ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select

For x = 1 To ActiveChart.SeriesCollection.Count
For y = 1 To ActiveChart.SeriesCollection(x).Points.Count
With ActiveChart.SeriesCollection(x)
If .Points(y).HasDataLabel Then
.Points(y).DataLabel.Characters(3, 1).Font.Superscript = True
End If
End With
Next y
Next x
End Sub

Now what i want to do is to determine if the datalabel has alphabet and
numbers, if only numbers then do nothing, but if it has alphabets as well
then superscript the alphabet part of the datalabel.
Hope some one could help me
Thanks a many.
Jyotsna

Hey Jim,

THanks for the reply. I really appriciate your efforts in helping novice
people like me... I did try the code, all it does is superscripts the 3rd
character of the datalabel even if the character is a number. May be I was
not clear of what I want. Sorry if I misled you.

But what I want to do is to check in each datalabel for an alphabet and if
there is then superscript that alphabet only. do not want to superscript the
3rd character like in the sub routine I pasted below. I found this code on
one of the posts and may be we can use that, I am not sure on how to apply
that. New to writing macros as you can see )

If Asc(Icurrent_char) >= 48 And Asc(Icurrent_char) <= 57 Then Exit Sub
If Icurrent_char = "-" Then Exit Sub
With DataLabel.Characters(Start:=Ii, Length:=1).Font
.Superscript = True


Thanks
J


"Jim Cone" wrote:

> J,
> See how this works for you.
> Jim Cone
> San Francisco, USA
>
> '---------------------------------------
> Sub Superscripting()
> Dim blnOK As Boolean
> Dim N As Long
> Dim x As Long
> Dim y As Long
> ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
>
> For x = 1 To ActiveChart.SeriesCollection.Count
> With ActiveChart.SeriesCollection(x)
> For y = 1 To .Points.Count
> If .Points(y).HasDataLabel Then
> For N = 1 To Len(.Points(y).DataLabel.Text)
> 'If any alpha character then ok to format.
> If Not Mid$(.Points(y).DataLabel.Text, N, 1) Like "#" Then
> blnOK = True
> Exit For
> End If
> Next 'N
> 'Make sure there are three characters.
> If blnOK And Len(.Points(y).DataLabel.Text) > 2 Then _
> .Points(y).DataLabel.Characters(3, 1).Font.Superscript = True
> End If
> blnOK = False
> Next y
> End With
> Next x
> End Sub
> '---------------------------------
>
>
> "Jyotsna" <[email protected]>
> wrote in message
> news:[email protected]...
> Hi all,
> I have a macro where the macro checks for the presence of datalabel and the
> superscripts the 3rd character. so far this works fine, here is the code
> below.
>
> Sub Superscripting()
> Dim x As Integer, y As Integer
> Dim Ix As Integer
> ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
> ActiveChart.SeriesCollection(1).DataLabels.Select
> 'ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
>
> For x = 1 To ActiveChart.SeriesCollection.Count
> For y = 1 To ActiveChart.SeriesCollection(x).Points.Count
> With ActiveChart.SeriesCollection(x)
> If .Points(y).HasDataLabel Then
> .Points(y).DataLabel.Characters(3, 1).Font.Superscript = True
> End If
> End With
> Next y
> Next x
> End Sub
>
> Now what i want to do is to determine if the datalabel has alphabet and
> numbers, if only numbers then do nothing, but if it has alphabets as well
> then superscript the alphabet part of the datalabel.
> Hope some one could help me
> Thanks a many.
> Jyotsna
>

This modification worked for me:

Sub Superscripting()
Dim blnOK As Boolean
Dim N As Long
Dim x As Long
Dim y As Long
Dim ipt As Long
Dim ilen As Long
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False

For x = 1 To ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(x)
For y = 1 To .Points.Count
blnOK = False
If .Points(y).HasDataLabel Then
For N = 1 To Len(.Points(y).DataLabel.Text)
'If any alpha character then ok to format.
If Not (Mid$(.Points(y).DataLabel.Text, N, 1) Like "#")
Then
If Not blnOK Then
ipt = N
ilen = 1
blnOK = True
Else
ilen = ilen + 1
End If
ElseIf blnOK Then
Exit For
End If
Next 'N
'Make sure there are three characters.
If blnOK And Len(.Points(y).DataLabel.Text) > 0 Then _
.Points(y).DataLabel.Characters(ipt,
ilen).Font.Superscript = True
End If
Next y
End With
Next x
End Sub

--
Regards,
Tom Ogilvy

"Jyotsna" <[email protected]> wrote in message
news:[email protected]...
> Hey Jim,
>
> THanks for the reply. I really appriciate your efforts in helping novice
> people like me... I did try the code, all it does is superscripts the 3rd
> character of the datalabel even if the character is a number. May be I was
> not clear of what I want. Sorry if I misled you.
>
> But what I want to do is to check in each datalabel for an alphabet and if
> there is then superscript that alphabet only. do not want to superscript
the
> 3rd character like in the sub routine I pasted below. I found this code on
> one of the posts and may be we can use that, I am not sure on how to apply
> that. New to writing macros as you can see )
>
> If Asc(Icurrent_char) >= 48 And Asc(Icurrent_char) <= 57 Then Exit Sub
> If Icurrent_char = "-" Then Exit Sub
> With DataLabel.Characters(Start:=Ii, Length:=1).Font
> .Superscript = True
>
>
> Thanks
> J
>
>
> "Jim Cone" wrote:
>
> > J,
> > See how this works for you.
> > Jim Cone
> > San Francisco, USA
> >
> > '---------------------------------------
> > Sub Superscripting()
> > Dim blnOK As Boolean
> > Dim N As Long
> > Dim x As Long
> > Dim y As Long
> > ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel,
LegendKey:=False
> >
> > For x = 1 To ActiveChart.SeriesCollection.Count
> > With ActiveChart.SeriesCollection(x)
> > For y = 1 To .Points.Count
> > If .Points(y).HasDataLabel Then
> > For N = 1 To Len(.Points(y).DataLabel.Text)
> > 'If any alpha character then ok to format.
> > If Not Mid$(.Points(y).DataLabel.Text, N, 1) Like
"#" Then
> > blnOK = True
> > Exit For
> > End If
> > Next 'N
> > 'Make sure there are three characters.
> > If blnOK And Len(.Points(y).DataLabel.Text) > 2 Then _
> > .Points(y).DataLabel.Characters(3,
1).Font.Superscript = True
> > End If
> > blnOK = False
> > Next y
> > End With
> > Next x
> > End Sub
> > '---------------------------------
> >
> >
> > "Jyotsna" <[email protected]>
> > wrote in message
> > news:[email protected]...
> > Hi all,
> > I have a macro where the macro checks for the presence of datalabel and
the
> > superscripts the 3rd character. so far this works fine, here is the code
> > below.
> >
> > Sub Superscripting()
> > Dim x As Integer, y As Integer
> > Dim Ix As Integer
> > ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel,
LegendKey:=False
> > ActiveChart.SeriesCollection(1).DataLabels.Select
> > 'ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
> >
> > For x = 1 To ActiveChart.SeriesCollection.Count
> > For y = 1 To ActiveChart.SeriesCollection(x).Points.Count
> > With ActiveChart.SeriesCollection(x)
> > If .Points(y).HasDataLabel Then
> > .Points(y).DataLabel.Characters(3, 1).Font.Superscript =
True
> > End If
> > End With
> > Next y
> > Next x
> > End Sub
> >
> > Now what i want to do is to determine if the datalabel has alphabet and
> > numbers, if only numbers then do nothing, but if it has alphabets as
well
> > then superscript the alphabet part of the datalabel.
> > Hope some one could help me
> > Thanks a many.
> > Jyotsna
> >

Jim cone and Tom thank you for all your help.

"Tom Ogilvy" wrote:

> This modification worked for me:
>
> Sub Superscripting()
> Dim blnOK As Boolean
> Dim N As Long
> Dim x As Long
> Dim y As Long
> Dim ipt As Long
> Dim ilen As Long
> ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
>
> For x = 1 To ActiveChart.SeriesCollection.Count
> With ActiveChart.SeriesCollection(x)
> For y = 1 To .Points.Count
> blnOK = False
> If .Points(y).HasDataLabel Then
> For N = 1 To Len(.Points(y).DataLabel.Text)
> 'If any alpha character then ok to format.
> If Not (Mid$(.Points(y).DataLabel.Text, N, 1) Like "#")
> Then
> If Not blnOK Then
> ipt = N
> ilen = 1
> blnOK = True
> Else
> ilen = ilen + 1
> End If
> ElseIf blnOK Then
> Exit For
> End If
> Next 'N
> 'Make sure there are three characters.
> If blnOK And Len(.Points(y).DataLabel.Text) > 0 Then _
> .Points(y).DataLabel.Characters(ipt,
> ilen).Font.Superscript = True
> End If
> Next y
> End With
> Next x
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Jyotsna" <[email protected]> wrote in message
> news:[email protected]...
> > Hey Jim,
> >
> > THanks for the reply. I really appriciate your efforts in helping novice
> > people like me... I did try the code, all it does is superscripts the 3rd
> > character of the datalabel even if the character is a number. May be I was
> > not clear of what I want. Sorry if I misled you.
> >
> > But what I want to do is to check in each datalabel for an alphabet and if
> > there is then superscript that alphabet only. do not want to superscript
> the
> > 3rd character like in the sub routine I pasted below. I found this code on
> > one of the posts and may be we can use that, I am not sure on how to apply
> > that. New to writing macros as you can see )
> >
> > If Asc(Icurrent_char) >= 48 And Asc(Icurrent_char) <= 57 Then Exit Sub
> > If Icurrent_char = "-" Then Exit Sub
> > With DataLabel.Characters(Start:=Ii, Length:=1).Font
> > .Superscript = True
> >
> >
> > Thanks
> > J
> >
> >
> > "Jim Cone" wrote:
> >
> > > J,
> > > See how this works for you.
> > > Jim Cone
> > > San Francisco, USA
> > >
> > > '---------------------------------------
> > > Sub Superscripting()
> > > Dim blnOK As Boolean
> > > Dim N As Long
> > > Dim x As Long
> > > Dim y As Long
> > > ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel,
> LegendKey:=False
> > >
> > > For x = 1 To ActiveChart.SeriesCollection.Count
> > > With ActiveChart.SeriesCollection(x)
> > > For y = 1 To .Points.Count
> > > If .Points(y).HasDataLabel Then
> > > For N = 1 To Len(.Points(y).DataLabel.Text)
> > > 'If any alpha character then ok to format.
> > > If Not Mid$(.Points(y).DataLabel.Text, N, 1) Like
> "#" Then
> > > blnOK = True
> > > Exit For
> > > End If
> > > Next 'N
> > > 'Make sure there are three characters.
> > > If blnOK And Len(.Points(y).DataLabel.Text) > 2 Then _
> > > .Points(y).DataLabel.Characters(3,
> 1).Font.Superscript = True
> > > End If
> > > blnOK = False
> > > Next y
> > > End With
> > > Next x
> > > End Sub
> > > '---------------------------------
> > >
> > >
> > > "Jyotsna" <[email protected]>
> > > wrote in message
> > > news:[email protected]...
> > > Hi all,
> > > I have a macro where the macro checks for the presence of datalabel and
> the
> > > superscripts the 3rd character. so far this works fine, here is the code
> > > below.
> > >
> > > Sub Superscripting()
> > > Dim x As Integer, y As Integer
> > > Dim Ix As Integer
> > > ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabel,
> LegendKey:=False
> > > ActiveChart.SeriesCollection(1).DataLabels.Select
> > > 'ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
> > >
> > > For x = 1 To ActiveChart.SeriesCollection.Count
> > > For y = 1 To ActiveChart.SeriesCollection(x).Points.Count
> > > With ActiveChart.SeriesCollection(x)
> > > If .Points(y).HasDataLabel Then
> > > .Points(y).DataLabel.Characters(3, 1).Font.Superscript =
> True
> > > End If
> > > End With
> > > Next y
> > > Next x
> > > End Sub
> > >
> > > Now what i want to do is to determine if the datalabel has alphabet and
> > > numbers, if only numbers then do nothing, but if it has alphabets as
> well
> > > then superscript the alphabet part of the datalabel.
> > > Hope some one could help me
> > > Thanks a many.
> > > Jyotsna
> > >
>
>
>