I have a loop that goes through data and puts it in another sheet to produce a chart. However, because I have a wide range of data I want the vertical axis' boundaries to change according to what the cell value is. I tried creating a section of the code to follow it. However, in this section only 3 lines are being read, the other lines get skipped. I ran the code then I stepped into it and that's when I noticed. Can anyone help me figure out why these lines are getting skipped?
The lines that are being skipped are:
cht.Axes(xlValue).MinimumScale = 10
ElseIf cell.Value >= 40 Then
cht.Axes(xlValue).MinimumScale = 20
ElseIf cell.Value >= 60 Then
cht.Axes(xlValue).MinimumScale = 40
Here's the full code:
Sub Loop()
Range("E2").Select
ActiveCell.Range("D1:E1").Select
Dim myPDF As String
Dim i As Long
For counter = 5 To 21
Sheets("Lang").Select
Range("'Lang'!$E$" & counter & ":$F$" & counter).Select 'numbers
Selection.Copy
Sheets("Lang-Chart").Select
Range("B1:C1").Select
ActiveSheet.Paste
Sheets("Lang").Select
Range("'Lang'!$G$" & counter & ":$I$" & counter).Select 'labels
Selection.Copy
Sheets("Lang-Chart").Select
Range("A2:C2").Select
ActiveSheet.Paste
Dim cht As Chart
Set cht = Worksheets("Lang-Chart").ChartObjects("Chart 2").Chart
For Each cell In Range("B1:C2")
If cell.Value >= 10 Then
cht.Axes(xlValue).MinimumScale = 10
ElseIf cell.Value < 10 Then
cht.Axes(xlValue).MinimumScale = 0
ElseIf cell.Value >= 40 Then
cht.Axes(xlValue).MinimumScale = 20
ElseIf cell.Value >= 60 Then
cht.Axes(xlValue).MinimumScale = 40
End If
Next cell
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
myPDF = "\\stchsfs\arboari$\Profile-Data\Desktop\Sample 20\c3-" & Sheets("Lang").Range("D" & i + 2).Value2 & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
i = i + 1
Next counter
End Sub
Thanks!
Aucun commentaire:
Enregistrer un commentaire