vendredi 27 avril 2018

Skipping lines in VBA (Loop/If Statements) - Chart Axis Boundaries changes

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