check for alphabet in datalabel and then superscript that letter

[Jyotsna]

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]

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

 

[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

 

[Tom Ogilvy]

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

 

[Jyotsna]

Jim cone and Tom thank you for all your help.

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