Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #734 > unrolled thread

I'm getting run time error 1004 and I need help editing my code

Started byJose Perez <jperezberrios@gmail.com>
First post2012-02-03 14:28 -0800
Last post2012-02-07 14:25 -0500
Articles 9 — 4 participants

Back to article view | Back to comp.lang.basic.visual.misc

Contents

  I'm getting run time error 1004 and I need help editing my code Jose Perez <jperezberrios@gmail.com> - 2012-02-03 14:28 -0800
    Re: I'm getting run time error 1004 and I need help editing my code GS <gs@somewhere.net> - 2012-02-07 12:58 -0500
      Re: I'm getting run time error 1004 and I need help editing my code "blank" <blank@blankety.blank.com> - 2012-02-08 05:08 +1100
        Re: I'm getting run time error 1004 and I need help editing my code GS <gs@somewhere.net> - 2012-02-07 14:59 -0500
          Re: I'm getting run time error 1004 and I need help editing my code "blank" <blank@blankety.blank.com> - 2012-02-08 07:09 +1100
            Re: I'm getting run time error 1004 and I need help editing my code GS <gs@somewhere.net> - 2012-02-07 15:56 -0500
              Re: I'm getting run time error 1004 and I need help editing my code "blank" <blank@blankety.blank.com> - 2012-02-08 10:48 +1100
      Re: I'm getting run time error 1004 and I need help editing my code "Auric__" <not.my.real@email.address> - 2012-02-07 19:15 +0000
        Re: I'm getting run time error 1004 and I need help editing my code GS <gs@somewhere.net> - 2012-02-07 14:25 -0500

#734 — I'm getting run time error 1004 and I need help editing my code

FromJose Perez <jperezberrios@gmail.com>
Date2012-02-03 14:28 -0800
SubjectI'm getting run time error 1004 and I need help editing my code
Message-ID<528cb6d4-caa7-4fb9-9bfa-e0f903ed2107@w4g2000vbc.googlegroups.com>
Hello,

I'm getting run time error 1004, AutoFill Method of Range Class
Failed. When I hit debug, it sends me to this line:

"Selection.AutoFill Destination:=Range("N5:" & g),
Type:=xlFillDefault"

This is on Ln 75, Col 5.

Also, when this code does run. It continues even when there is no more
data to process. I would like if it had a loop that stopped when there
is no more "raw data".

Here is the code:

Sub proFirst()


        Dim name As String
        name = "source"
        Sheets(1).Select
        ActiveSheet.name = name

    Range("B1").Select
    If Selection.Value = "Number of Active Material (g)" Then
    Else
        Rows("1:1").Select
        Selection.Insert Shift:=x1Down
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
        Columns("B:B").EntireColumn.AutoFit
    End If

    Dim numGrams As String
    numGrams = InputBox("Input number of Active Material (g).")
    Range("C1").Select
    ActiveCell.Offset(0, 0).FormulaR1C1 = numGrams

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
    Columns("B:B").EntireColumn.AutoFit

    Range("D1").Select
    If Selection.Value = "Number of Cycles" Then

    Else
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Number of Cycles"
        Columns("D:D").EntireColumn.AutoFit
    End If

    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Number of Cycles"
    Columns("D:D").EntireColumn.AutoFit

   'If cycles are more than 100, change value
    Range("E1").Select
    ActiveCell.Offset(0, 0).FormulaR1C1 = "100"

    'If OCP is more than 50 data points, change the range
    Dim rng As Range
    Dim i As Integer, counter As Integer
    Set rng = Range("H1:H50")
    i = 1
    For counter = 1 To rng.Rows.Count
        If rng.Cells(i) = "0" Then
            rng.Cells(i).EntireRow.Delete
        Else
            i = i + 1
        End If
    Next



    'Find end value
    Sheets("source").Select

    Range("A5").Select
    Do Until IsEmpty(ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
    Loop
    c = ActiveCell.AddressLocal
    Range(c).Offset(-1, 0).Select
    r = ActiveCell.Row
    g = "N" & r
    Range("N5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-6]=0,""OCP"",IF(RC[-6]>0,""charge"",""discharge""))"
    Range("N5").Select
    Selection.AutoFill Destination:=Range("N5:" & g),
Type:=xlFillDefault
    Range("N5:" & g).Select

    Sheets.Add.name = "Conclusion"

    'Move source into worksheets
    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a
            If IsEmpty(ActiveCell.Value) And
IsEmpty(ActiveCell.Offset(10, 0).Value) Then
             b = b + 1
            Else
            If bb <> 1 Then
            Sheets.Add.name = "Cycle " & b
            Sheets("source").Select

            Rows("4:4").Select
            Selection.Copy
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("A2").Select
            Selection.PasteSpecial
            ActiveSheet.Range("A1").Select
            ActiveCell.FormulaR1C1 = "Cycle " & b

            Sheets("Conclusion").Select
            ActiveSheet.Range("A1").Select
            ActiveCell.FormulaR1C1 = "Conclusion"

            Sheets("source").Select
            Range("N1").Select

            Do Until ActiveCell.Value = "discharge"
                ActiveCell.Offset(1, 0).Select
            Loop
            d = ActiveCell.Row
            Do Until ActiveCell.Value = "charge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            If IsEmpty(ActiveCell.Value) Then
            bb = 1
            cc = b
            End If

            Do Until ActiveCell.Value = "discharge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            c = ActiveCell.AddressLocal
            Range(c).Offset(-1, 0).Select
            e = ActiveCell.Row
            f = d & ":" & e
            Rows(f).Select
            Selection.Cut
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("A3").Select
            ActiveSheet.Paste
            End If
            b = b + 1
            End If
        Next
    Else
    End If

    If cc < a Then
        Sheets("source").Select
        Range("E1").Select
        ActiveCell.FormulaR1C1 = cc
    Else
    End If
    'Add Equations to Cycles
    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a
        Sheets("Cycle " & b).Select
        ActiveSheet.Range("A3").Select
        Do Until IsEmpty(ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
        Loop
            c = ActiveCell.AddressLocal
            ActiveSheet.Range(c).Offset(-1, 0).Select
            r = ActiveCell.Row

        Sheets("Conclusion").Select

        Sheets("Cycle " & b).Select
        ActiveSheet.Range("O2").Select
        ActiveCell.FormulaR1C1 = "mAh/g"

        ActiveSheet.Range("O3").Select
        ActiveCell.FormulaR1C1 = "=ABS(RC[-7]*RC[1]/source!R1C3/3600)"
        ActiveSheet.Range("O3").Select
        Selection.AutoFill Destination:=ActiveSheet.Range("O3:O" & r),
Type:=xlFillDefault
        ActiveSheet.Range("O3:O" & r).Select

        ActiveSheet.Range("P2").Select
        ActiveCell.FormulaR1C1 = "t per cycle"

        ActiveSheet.Range("P3").Select
        ActiveCell.FormulaR1C1 = "=RC[-9]-R3C7"
        ActiveSheet.Range("P3").Select
        Selection.AutoFill Destination:=ActiveSheet.Range("P3:P" & r),
Type:=xlFillDefault
        ActiveSheet.Range("P3:P" & r).Select

        'Build Charts

        ActiveSheet.Range("I3:I" & r).Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Cycle " &
b).Range("I3:I" & r), PlotBy _
        :=xlColumns
    ActiveChart.SeriesCollection(1).XValues = "='Cycle " & b & "'!
R3C15:R" & r & "C15"
    ActiveChart.SeriesCollection(1).name = "Cycle " & b
    ActiveChart.Location Where:=xlLocationAsObject, name:="Cycle " & b
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Cycle " & b
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Capacity (mAh/g)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
(V)"
    End With
    ActiveChart.HasDataTable = False




        b = b + 1

        Next
    Else
    End If

    Sheets("Conclusion").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData
Source:=Sheets("Conclusion").Range("A3"), PlotBy _
        :=xlRows

    a = Sheets("source").Range("E1").FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a

        c = Sheets("Cycle " & b).Range("A1").End(xlDown).Offset(1,
0).Address
        r = Sheets("Cycle " & b).Range(c).Offset(-1, 0).Row

        If (b = 1) Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(1).Values = "='Cycle 1'!
R30C9"
            ActiveChart.SeriesCollection(1).XValues = "='Cycle 1'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(1).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(1).name = "=""Cycle 1"""
        End If
        If b = 2 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(2).XValues = "='Cycle 2'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(2).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(2).name = "=""Cycle 2"""
        End If
        If b = 3 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(3).XValues = "='Cycle 3'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(3).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(3).name = "=""Cycle 3"""
        End If
        If b = 4 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(4).XValues = "='Cycle 4'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(4).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(4).name = "=""Cycle 4"""
        End If
        If b = 5 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(5).XValues = "='Cycle 5'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(5).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(5).name = "=""Cycle 5"""
        End If
        If b = 6 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(6).XValues = "='Cycle 6'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(6).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(6).name = "=""Cycle 6"""
        End If
        If b = 7 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(7).XValues = "='Cycle 7'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(7).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(7).name = "=""Cycle 7"""
        End If
        If b = 8 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(8).XValues = "='Cycle 8'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(8).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(8).name = "=""Cycle 8"""
        End If
        If b = 9 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(9).XValues = "='Cycle 9'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(9).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(9).name = "=""Cycle 9"""
        End If
        If b = 10 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(10).XValues = "='Cycle 10'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(10).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(10).name = "=""Cycle 10"""
        End If
        b = b + 1
        Next
    End If



    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Conclusion"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
        "Capacity (m Ah/g)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
(V)"
    End With
    ActiveChart.HasDataTable = False

    'Add Conclusion Values

    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    x = 3
    If b <= a Then
        For counter = 1 To a
            If IsEmpty(ActiveCell.Value) And
IsEmpty(ActiveCell.Offset(10, 0).Value) Then
             b = b + 1
            Else
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("N1").Select
            Do Until ActiveCell.Value = "discharge"
                ActiveCell.Offset(1, 0).Select
            Loop
            Do Until ActiveCell.Value = "charge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            v = ActiveCell.Row
            r = ActiveCell.Offset(-1, 1).Row
            t = ActiveCell.Offset(-1, 1).Column
            If IsEmpty(ActiveCell.Value) Then
            aa = 1
            End If
            Do Until ActiveCell.Value = "discharge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            s = ActiveCell.Offset(-1, 1).Row
            u = ActiveCell.Offset(-1, 1).Column

            'Corrects for time
            If aa <> 1 Then
            ActiveSheet.Range("P" & v).Select
            ActiveCell.FormulaR1C1 = "=RC[-9]-R" & v & "C7"
            ActiveSheet.Range("P" & v).Select
            Selection.AutoFill Destination:=ActiveSheet.Range("P" & v
& ":P" & s), Type:=xlFillDefault
            ActiveSheet.Range("P" & v & ":P" & s).Select
            End If

            Sheets("Conclusion").Select

            ActiveSheet.Range("A" & x).Select
            ActiveCell.FormulaR1C1 = "Cycle " & b
            ActiveCell.Offset(1, 0).Select
            ActiveCell.FormulaR1C1 = "Discharge :"
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & r & "C"
& t
            ActiveCell.Offset(1, -1).Select
            ActiveCell.FormulaR1C1 = "Charge :"
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & s & "C"
& u

            x = x + 4
            b = b + 1

            End If
        Next
    Else
    End If




End Sub

[toc] | [next] | [standalone]

#735

FromGS <gs@somewhere.net>
Date2012-02-07 12:58 -0500
Message-ID<jgrons$jbg$1@dont-email.me>
In reply to#734
Lots of issues with this!
Firstly, you need to ask your Q in an Excel group. This group is for 
Classic VB NOT Excel VBA!

Secondly, obviously your code is a rework of using the most inefficient 
macro recorder.

Thirdly, none of your variables are declared.

Fourthly, there's way, way, way too much unnecessary range selecting 
going on, making for rather poor performance.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

[toc] | [prev] | [next] | [standalone]

#736

From"blank" <blank@blankety.blank.com>
Date2012-02-08 05:08 +1100
Message-ID<4f3168b9$1@dnews.tpgi.com.au>
In reply to#735
This is odd. I have the reply here but not the original post!

"GS" <gs@somewhere.net> wrote in message news:jgrons$jbg$1@dont-email.me...
> Lots of issues with this!
> Firstly, you need to ask your Q in an Excel group. This group is for 
> Classic VB NOT Excel VBA!
>
> Secondly, obviously your code is a rework of using the most inefficient 
> macro recorder.
>
> Thirdly, none of your variables are declared.
>
> Fourthly, there's way, way, way too much unnecessary range selecting going 
> on, making for rather poor performance.
>
> -- 
> Garry
>
> Free usenet access at http://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
>

[toc] | [prev] | [next] | [standalone]

#739

FromGS <gs@somewhere.net>
Date2012-02-07 14:59 -0500
Message-ID<jgrvrl$1sq$1@dont-email.me>
In reply to#736
blank explained on 2/7/2012 :
> This is odd. I have the reply here but not the original post!
>
Too many lines in the original post to include in my reply!<g>

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

[toc] | [prev] | [next] | [standalone]

#740

From"blank" <blank@blankety.blank.com>
Date2012-02-08 07:09 +1100
Message-ID<4f3184ec@dnews.tpgi.com.au>
In reply to#739
I meant I can't even see the original post, or the code, which would be 
interesting.

"GS" <gs@somewhere.net> wrote in message news:jgrvrl$1sq$1@dont-email.me...
> blank explained on 2/7/2012 :
>> This is odd. I have the reply here but not the original post!
>>
> Too many lines in the original post to include in my reply!<g>
>
> -- 
> Garry
>
> Free usenet access at http://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
>

[toc] | [prev] | [next] | [standalone]

#741

FromGS <gs@somewhere.net>
Date2012-02-07 15:56 -0500
Message-ID<jgs36i$t78$1@dont-email.me>
In reply to#740
Here ya' go...

Hello,

I'm getting run time error 1004, AutoFill Method of Range Class
Failed. When I hit debug, it sends me to this line:

"Selection.AutoFill Destination:=Range("N5:" & g),
Type:=xlFillDefault"

This is on Ln 75, Col 5.

Also, when this code does run. It continues even when there is no more
data to process. I would like if it had a loop that stopped when there
is no more "raw data".

Here is the code:

Sub proFirst()


        Dim name As String
        name = "source"
        Sheets(1).Select
        ActiveSheet.name = name

    Range("B1").Select
    If Selection.Value = "Number of Active Material (g)" Then
    Else
        Rows("1:1").Select
        Selection.Insert Shift:=x1Down
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
        Columns("B:B").EntireColumn.AutoFit
    End If

    Dim numGrams As String
    numGrams = InputBox("Input number of Active Material (g).")
    Range("C1").Select
    ActiveCell.Offset(0, 0).FormulaR1C1 = numGrams

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
    Columns("B:B").EntireColumn.AutoFit

    Range("D1").Select
    If Selection.Value = "Number of Cycles" Then

    Else
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Number of Cycles"
        Columns("D:D").EntireColumn.AutoFit
    End If

    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Number of Cycles"
    Columns("D:D").EntireColumn.AutoFit

   'If cycles are more than 100, change value
    Range("E1").Select
    ActiveCell.Offset(0, 0).FormulaR1C1 = "100"

    'If OCP is more than 50 data points, change the range
    Dim rng As Range
    Dim i As Integer, counter As Integer
    Set rng = Range("H1:H50")
    i = 1
    For counter = 1 To rng.Rows.Count
        If rng.Cells(i) = "0" Then
            rng.Cells(i).EntireRow.Delete
        Else
            i = i + 1
        End If
    Next



    'Find end value
    Sheets("source").Select

    Range("A5").Select
    Do Until IsEmpty(ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
    Loop
    c = ActiveCell.AddressLocal
    Range(c).Offset(-1, 0).Select
    r = ActiveCell.Row
    g = "N" & r
    Range("N5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-6]=0,""OCP"",IF(RC[-6]>0,""charge"",""discharge""))"
    Range("N5").Select
    Selection.AutoFill Destination:=Range("N5:" & g),
Type:=xlFillDefault
    Range("N5:" & g).Select

    Sheets.Add.name = "Conclusion"

    'Move source into worksheets
    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a
            If IsEmpty(ActiveCell.Value) And
IsEmpty(ActiveCell.Offset(10, 0).Value) Then
             b = b + 1
            Else
            If bb <> 1 Then
            Sheets.Add.name = "Cycle " & b
            Sheets("source").Select

            Rows("4:4").Select
            Selection.Copy
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("A2").Select
            Selection.PasteSpecial
            ActiveSheet.Range("A1").Select
            ActiveCell.FormulaR1C1 = "Cycle " & b

            Sheets("Conclusion").Select
            ActiveSheet.Range("A1").Select
            ActiveCell.FormulaR1C1 = "Conclusion"

            Sheets("source").Select
            Range("N1").Select

            Do Until ActiveCell.Value = "discharge"
                ActiveCell.Offset(1, 0).Select
            Loop
            d = ActiveCell.Row
            Do Until ActiveCell.Value = "charge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            If IsEmpty(ActiveCell.Value) Then
            bb = 1
            cc = b
            End If

            Do Until ActiveCell.Value = "discharge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            c = ActiveCell.AddressLocal
            Range(c).Offset(-1, 0).Select
            e = ActiveCell.Row
            f = d & ":" & e
            Rows(f).Select
            Selection.Cut
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("A3").Select
            ActiveSheet.Paste
            End If
            b = b + 1
            End If
        Next
    Else
    End If

    If cc < a Then
        Sheets("source").Select
        Range("E1").Select
        ActiveCell.FormulaR1C1 = cc
    Else
    End If
    'Add Equations to Cycles
    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a
        Sheets("Cycle " & b).Select
        ActiveSheet.Range("A3").Select
        Do Until IsEmpty(ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
        Loop
            c = ActiveCell.AddressLocal
            ActiveSheet.Range(c).Offset(-1, 0).Select
            r = ActiveCell.Row

        Sheets("Conclusion").Select

        Sheets("Cycle " & b).Select
        ActiveSheet.Range("O2").Select
        ActiveCell.FormulaR1C1 = "mAh/g"

        ActiveSheet.Range("O3").Select
        ActiveCell.FormulaR1C1 = "=ABS(RC[-7]*RC[1]/source!R1C3/3600)"
        ActiveSheet.Range("O3").Select
        Selection.AutoFill Destination:=ActiveSheet.Range("O3:O" & r),
Type:=xlFillDefault
        ActiveSheet.Range("O3:O" & r).Select

        ActiveSheet.Range("P2").Select
        ActiveCell.FormulaR1C1 = "t per cycle"

        ActiveSheet.Range("P3").Select
        ActiveCell.FormulaR1C1 = "=RC[-9]-R3C7"
        ActiveSheet.Range("P3").Select
        Selection.AutoFill Destination:=ActiveSheet.Range("P3:P" & r),
Type:=xlFillDefault
        ActiveSheet.Range("P3:P" & r).Select

        'Build Charts

        ActiveSheet.Range("I3:I" & r).Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Cycle " &
b).Range("I3:I" & r), PlotBy _
        :=xlColumns
    ActiveChart.SeriesCollection(1).XValues = "='Cycle " & b & "'!
R3C15:R" & r & "C15"
    ActiveChart.SeriesCollection(1).name = "Cycle " & b
    ActiveChart.Location Where:=xlLocationAsObject, name:="Cycle " & b
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Cycle " & b
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Capacity (mAh/g)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
(V)"
    End With
    ActiveChart.HasDataTable = False




        b = b + 1

        Next
    Else
    End If

    Sheets("Conclusion").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData
Source:=Sheets("Conclusion").Range("A3"), PlotBy _
        :=xlRows

    a = Sheets("source").Range("E1").FormulaR1C1
    b = 1
    If b <= a Then
        For counter = 1 To a

        c = Sheets("Cycle " & b).Range("A1").End(xlDown).Offset(1,
0).Address
        r = Sheets("Cycle " & b).Range(c).Offset(-1, 0).Row

        If (b = 1) Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(1).Values = "='Cycle 1'!
R30C9"
            ActiveChart.SeriesCollection(1).XValues = "='Cycle 1'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(1).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(1).name = "=""Cycle 1"""
        End If
        If b = 2 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(2).XValues = "='Cycle 2'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(2).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(2).name = "=""Cycle 2"""
        End If
        If b = 3 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(3).XValues = "='Cycle 3'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(3).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(3).name = "=""Cycle 3"""
        End If
        If b = 4 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(4).XValues = "='Cycle 4'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(4).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(4).name = "=""Cycle 4"""
        End If
        If b = 5 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(5).XValues = "='Cycle 5'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(5).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(5).name = "=""Cycle 5"""
        End If
        If b = 6 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(6).XValues = "='Cycle 6'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(6).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(6).name = "=""Cycle 6"""
        End If
        If b = 7 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(7).XValues = "='Cycle 7'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(7).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(7).name = "=""Cycle 7"""
        End If
        If b = 8 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(8).XValues = "='Cycle 8'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(8).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(8).name = "=""Cycle 8"""
        End If
        If b = 9 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(9).XValues = "='Cycle 9'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(9).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(9).name = "=""Cycle 9"""
        End If
        If b = 10 Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(10).XValues = "='Cycle 10'!
R3C15:R" & r & "C15"
            ActiveChart.SeriesCollection(10).Values = "='Cycle " & b &
"'!R3C9:R" & r & "C9"
            ActiveChart.SeriesCollection(10).name = "=""Cycle 10"""
        End If
        b = b + 1
        Next
    End If



    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Conclusion"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
        "Capacity (m Ah/g)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
(V)"
    End With
    ActiveChart.HasDataTable = False

    'Add Conclusion Values

    Sheets("source").Select
    Range("E1").Select
    a = ActiveCell.FormulaR1C1
    b = 1
    x = 3
    If b <= a Then
        For counter = 1 To a
            If IsEmpty(ActiveCell.Value) And
IsEmpty(ActiveCell.Offset(10, 0).Value) Then
             b = b + 1
            Else
            Sheets("Cycle " & b).Select
            ActiveSheet.Range("N1").Select
            Do Until ActiveCell.Value = "discharge"
                ActiveCell.Offset(1, 0).Select
            Loop
            Do Until ActiveCell.Value = "charge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            v = ActiveCell.Row
            r = ActiveCell.Offset(-1, 1).Row
            t = ActiveCell.Offset(-1, 1).Column
            If IsEmpty(ActiveCell.Value) Then
            aa = 1
            End If
            Do Until ActiveCell.Value = "discharge" Or
IsEmpty(ActiveCell.Value)
                ActiveCell.Offset(1, 0).Select
            Loop
            s = ActiveCell.Offset(-1, 1).Row
            u = ActiveCell.Offset(-1, 1).Column

            'Corrects for time
            If aa <> 1 Then
            ActiveSheet.Range("P" & v).Select
            ActiveCell.FormulaR1C1 = "=RC[-9]-R" & v & "C7"
            ActiveSheet.Range("P" & v).Select
            Selection.AutoFill Destination:=ActiveSheet.Range("P" & v
& "" & s), Type:=xlFillDefault
            ActiveSheet.Range("P" & v & "" & s).Select
            End If

            Sheets("Conclusion").Select

            ActiveSheet.Range("A" & x).Select
            ActiveCell.FormulaR1C1 = "Cycle " & b
            ActiveCell.Offset(1, 0).Select
            ActiveCell.FormulaR1C1 = "Discharge :"
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & r & "C"
& t
            ActiveCell.Offset(1, -1).Select
            ActiveCell.FormulaR1C1 = "Charge :"
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & s & "C"
& u

            x = x + 4
            b = b + 1

            End If
        Next
    Else
    End If




End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

[toc] | [prev] | [next] | [standalone]

#743

From"blank" <blank@blankety.blank.com>
Date2012-02-08 10:48 +1100
Message-ID<4f31b845$1@dnews.tpgi.com.au>
In reply to#741
Great Scott Garry!

I shouldn't have asked!  Much of that is way over my head!

"GS" <gs@somewhere.net> wrote in message news:jgs36i$t78$1@dont-email.me...
> Here ya' go...
>
> Hello,
>
> I'm getting run time error 1004, AutoFill Method of Range Class
> Failed. When I hit debug, it sends me to this line:
>
> "Selection.AutoFill Destination:=Range("N5:" & g),
> Type:=xlFillDefault"
>
> This is on Ln 75, Col 5.
>
> Also, when this code does run. It continues even when there is no more
> data to process. I would like if it had a loop that stopped when there
> is no more "raw data".
>
> Here is the code:
>
> Sub proFirst()
>
>
>        Dim name As String
>        name = "source"
>        Sheets(1).Select
>        ActiveSheet.name = name
>
>    Range("B1").Select
>    If Selection.Value = "Number of Active Material (g)" Then
>    Else
>        Rows("1:1").Select
>        Selection.Insert Shift:=x1Down
>        Range("B1").Select
>        ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
>        Columns("B:B").EntireColumn.AutoFit
>    End If
>
>    Dim numGrams As String
>    numGrams = InputBox("Input number of Active Material (g).")
>    Range("C1").Select
>    ActiveCell.Offset(0, 0).FormulaR1C1 = numGrams
>
>    Range("B1").Select
>    ActiveCell.FormulaR1C1 = "Number of Active Material (g)"
>    Columns("B:B").EntireColumn.AutoFit
>
>    Range("D1").Select
>    If Selection.Value = "Number of Cycles" Then
>
>    Else
>        Range("D1").Select
>        ActiveCell.FormulaR1C1 = "Number of Cycles"
>        Columns("D:D").EntireColumn.AutoFit
>    End If
>
>    Range("D1").Select
>    ActiveCell.FormulaR1C1 = "Number of Cycles"
>    Columns("D:D").EntireColumn.AutoFit
>
>   'If cycles are more than 100, change value
>    Range("E1").Select
>    ActiveCell.Offset(0, 0).FormulaR1C1 = "100"
>
>    'If OCP is more than 50 data points, change the range
>    Dim rng As Range
>    Dim i As Integer, counter As Integer
>    Set rng = Range("H1:H50")
>    i = 1
>    For counter = 1 To rng.Rows.Count
>        If rng.Cells(i) = "0" Then
>            rng.Cells(i).EntireRow.Delete
>        Else
>            i = i + 1
>        End If
>    Next
>
>
>
>    'Find end value
>    Sheets("source").Select
>
>    Range("A5").Select
>    Do Until IsEmpty(ActiveCell.Value)
>        ActiveCell.Offset(1, 0).Select
>    Loop
>    c = ActiveCell.AddressLocal
>    Range(c).Offset(-1, 0).Select
>    r = ActiveCell.Row
>    g = "N" & r
>    Range("N5").Select
>    ActiveCell.FormulaR1C1 = _
>        "=IF(RC[-6]=0,""OCP"",IF(RC[-6]>0,""charge"",""discharge""))"
>    Range("N5").Select
>    Selection.AutoFill Destination:=Range("N5:" & g),
> Type:=xlFillDefault
>    Range("N5:" & g).Select
>
>    Sheets.Add.name = "Conclusion"
>
>    'Move source into worksheets
>    Sheets("source").Select
>    Range("E1").Select
>    a = ActiveCell.FormulaR1C1
>    b = 1
>    If b <= a Then
>        For counter = 1 To a
>            If IsEmpty(ActiveCell.Value) And
> IsEmpty(ActiveCell.Offset(10, 0).Value) Then
>             b = b + 1
>            Else
>            If bb <> 1 Then
>            Sheets.Add.name = "Cycle " & b
>            Sheets("source").Select
>
>            Rows("4:4").Select
>            Selection.Copy
>            Sheets("Cycle " & b).Select
>            ActiveSheet.Range("A2").Select
>            Selection.PasteSpecial
>            ActiveSheet.Range("A1").Select
>            ActiveCell.FormulaR1C1 = "Cycle " & b
>
>            Sheets("Conclusion").Select
>            ActiveSheet.Range("A1").Select
>            ActiveCell.FormulaR1C1 = "Conclusion"
>
>            Sheets("source").Select
>            Range("N1").Select
>
>            Do Until ActiveCell.Value = "discharge"
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            d = ActiveCell.Row
>            Do Until ActiveCell.Value = "charge" Or
> IsEmpty(ActiveCell.Value)
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            If IsEmpty(ActiveCell.Value) Then
>            bb = 1
>            cc = b
>            End If
>
>            Do Until ActiveCell.Value = "discharge" Or
> IsEmpty(ActiveCell.Value)
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            c = ActiveCell.AddressLocal
>            Range(c).Offset(-1, 0).Select
>            e = ActiveCell.Row
>            f = d & ":" & e
>            Rows(f).Select
>            Selection.Cut
>            Sheets("Cycle " & b).Select
>            ActiveSheet.Range("A3").Select
>            ActiveSheet.Paste
>            End If
>            b = b + 1
>            End If
>        Next
>    Else
>    End If
>
>    If cc < a Then
>        Sheets("source").Select
>        Range("E1").Select
>        ActiveCell.FormulaR1C1 = cc
>    Else
>    End If
>    'Add Equations to Cycles
>    Sheets("source").Select
>    Range("E1").Select
>    a = ActiveCell.FormulaR1C1
>    b = 1
>    If b <= a Then
>        For counter = 1 To a
>        Sheets("Cycle " & b).Select
>        ActiveSheet.Range("A3").Select
>        Do Until IsEmpty(ActiveCell.Value)
>        ActiveCell.Offset(1, 0).Select
>        Loop
>            c = ActiveCell.AddressLocal
>            ActiveSheet.Range(c).Offset(-1, 0).Select
>            r = ActiveCell.Row
>
>        Sheets("Conclusion").Select
>
>        Sheets("Cycle " & b).Select
>        ActiveSheet.Range("O2").Select
>        ActiveCell.FormulaR1C1 = "mAh/g"
>
>        ActiveSheet.Range("O3").Select
>        ActiveCell.FormulaR1C1 = "=ABS(RC[-7]*RC[1]/source!R1C3/3600)"
>        ActiveSheet.Range("O3").Select
>        Selection.AutoFill Destination:=ActiveSheet.Range("O3:O" & r),
> Type:=xlFillDefault
>        ActiveSheet.Range("O3:O" & r).Select
>
>        ActiveSheet.Range("P2").Select
>        ActiveCell.FormulaR1C1 = "t per cycle"
>
>        ActiveSheet.Range("P3").Select
>        ActiveCell.FormulaR1C1 = "=RC[-9]-R3C7"
>        ActiveSheet.Range("P3").Select
>        Selection.AutoFill Destination:=ActiveSheet.Range("P3:P" & r),
> Type:=xlFillDefault
>        ActiveSheet.Range("P3:P" & r).Select
>
>        'Build Charts
>
>        ActiveSheet.Range("I3:I" & r).Select
>    Charts.Add
>    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
>    ActiveChart.SetSourceData Source:=Sheets("Cycle " &
> b).Range("I3:I" & r), PlotBy _
>        :=xlColumns
>    ActiveChart.SeriesCollection(1).XValues = "='Cycle " & b & "'!
> R3C15:R" & r & "C15"
>    ActiveChart.SeriesCollection(1).name = "Cycle " & b
>    ActiveChart.Location Where:=xlLocationAsObject, name:="Cycle " & b
>    With ActiveChart
>        .HasTitle = True
>        .ChartTitle.Characters.Text = "Cycle " & b
>        .Axes(xlCategory, xlPrimary).HasTitle = True
>        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> "Capacity (mAh/g)"
>        .Axes(xlValue, xlPrimary).HasTitle = True
>        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
> (V)"
>    End With
>    ActiveChart.HasDataTable = False
>
>
>
>
>        b = b + 1
>
>        Next
>    Else
>    End If
>
>    Sheets("Conclusion").Select
>    Charts.Add
>    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
>    ActiveChart.SetSourceData
> Source:=Sheets("Conclusion").Range("A3"), PlotBy _
>        :=xlRows
>
>    a = Sheets("source").Range("E1").FormulaR1C1
>    b = 1
>    If b <= a Then
>        For counter = 1 To a
>
>        c = Sheets("Cycle " & b).Range("A1").End(xlDown).Offset(1,
> 0).Address
>        r = Sheets("Cycle " & b).Range(c).Offset(-1, 0).Row
>
>        If (b = 1) Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(1).Values = "='Cycle 1'!
> R30C9"
>            ActiveChart.SeriesCollection(1).XValues = "='Cycle 1'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(1).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(1).name = "=""Cycle 1"""
>        End If
>        If b = 2 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(2).XValues = "='Cycle 2'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(2).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(2).name = "=""Cycle 2"""
>        End If
>        If b = 3 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(3).XValues = "='Cycle 3'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(3).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(3).name = "=""Cycle 3"""
>        End If
>        If b = 4 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(4).XValues = "='Cycle 4'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(4).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(4).name = "=""Cycle 4"""
>        End If
>        If b = 5 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(5).XValues = "='Cycle 5'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(5).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(5).name = "=""Cycle 5"""
>        End If
>        If b = 6 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(6).XValues = "='Cycle 6'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(6).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(6).name = "=""Cycle 6"""
>        End If
>        If b = 7 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(7).XValues = "='Cycle 7'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(7).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(7).name = "=""Cycle 7"""
>        End If
>        If b = 8 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(8).XValues = "='Cycle 8'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(8).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(8).name = "=""Cycle 8"""
>        End If
>        If b = 9 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(9).XValues = "='Cycle 9'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(9).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(9).name = "=""Cycle 9"""
>        End If
>        If b = 10 Then
>            ActiveChart.SeriesCollection.NewSeries
>            ActiveChart.SeriesCollection(10).XValues = "='Cycle 10'!
> R3C15:R" & r & "C15"
>            ActiveChart.SeriesCollection(10).Values = "='Cycle " & b &
> "'!R3C9:R" & r & "C9"
>            ActiveChart.SeriesCollection(10).name = "=""Cycle 10"""
>        End If
>        b = b + 1
>        Next
>    End If
>
>
>
>    ActiveChart.Location Where:=xlLocationAsNewSheet
>    With ActiveChart
>        .HasTitle = True
>        .ChartTitle.Characters.Text = "Conclusion"
>        .Axes(xlCategory, xlPrimary).HasTitle = True
>        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
>        "Capacity (m Ah/g)"
>        .Axes(xlValue, xlPrimary).HasTitle = True
>        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage
> (V)"
>    End With
>    ActiveChart.HasDataTable = False
>
>    'Add Conclusion Values
>
>    Sheets("source").Select
>    Range("E1").Select
>    a = ActiveCell.FormulaR1C1
>    b = 1
>    x = 3
>    If b <= a Then
>        For counter = 1 To a
>            If IsEmpty(ActiveCell.Value) And
> IsEmpty(ActiveCell.Offset(10, 0).Value) Then
>             b = b + 1
>            Else
>            Sheets("Cycle " & b).Select
>            ActiveSheet.Range("N1").Select
>            Do Until ActiveCell.Value = "discharge"
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            Do Until ActiveCell.Value = "charge" Or
> IsEmpty(ActiveCell.Value)
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            v = ActiveCell.Row
>            r = ActiveCell.Offset(-1, 1).Row
>            t = ActiveCell.Offset(-1, 1).Column
>            If IsEmpty(ActiveCell.Value) Then
>            aa = 1
>            End If
>            Do Until ActiveCell.Value = "discharge" Or
> IsEmpty(ActiveCell.Value)
>                ActiveCell.Offset(1, 0).Select
>            Loop
>            s = ActiveCell.Offset(-1, 1).Row
>            u = ActiveCell.Offset(-1, 1).Column
>
>            'Corrects for time
>            If aa <> 1 Then
>            ActiveSheet.Range("P" & v).Select
>            ActiveCell.FormulaR1C1 = "=RC[-9]-R" & v & "C7"
>            ActiveSheet.Range("P" & v).Select
>            Selection.AutoFill Destination:=ActiveSheet.Range("P" & v
> & "" & s), Type:=xlFillDefault
>            ActiveSheet.Range("P" & v & "" & s).Select
>            End If
>
>            Sheets("Conclusion").Select
>
>            ActiveSheet.Range("A" & x).Select
>            ActiveCell.FormulaR1C1 = "Cycle " & b
>            ActiveCell.Offset(1, 0).Select
>            ActiveCell.FormulaR1C1 = "Discharge :"
>            ActiveCell.Offset(0, 1).Select
>            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & r & "C"
> & t
>            ActiveCell.Offset(1, -1).Select
>            ActiveCell.FormulaR1C1 = "Charge :"
>            ActiveCell.Offset(0, 1).Select
>            ActiveCell.FormulaR1C1 = "='Cycle " & b & "'!R" & s & "C"
> & u
>
>            x = x + 4
>            b = b + 1
>
>            End If
>        Next
>    Else
>    End If
>
>
>
>
> End Sub
>
> -- 
> Garry
>
> Free usenet access at http://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
>

[toc] | [prev] | [next] | [standalone]

#737

From"Auric__" <not.my.real@email.address>
Date2012-02-07 19:15 +0000
Message-ID<Xns9FF27CC6A1195auricauricauricauric@88.198.244.100>
In reply to#735
GS wrote:

> Lots of issues with this!
> Firstly, you need to ask your Q in an Excel group. This group is for 
> Classic VB NOT Excel VBA!
>
> Secondly, obviously your code is a rework of using the most inefficient 
> macro recorder.
>
> Thirdly, none of your variables are declared.
>
> Fourthly, there's way, way, way too much unnecessary range selecting 
> going on, making for rather poor performance.

Gee, Garry, tell us how you *really* feel. ;-)

-- 
If you cannot convince them, confuse them.
 -- Harry S Truman

[toc] | [prev] | [next] | [standalone]

#738

FromGS <gs@somewhere.net>
Date2012-02-07 14:25 -0500
Message-ID<jgrtqc$jfo$1@dont-email.me>
In reply to#737
After serious thinking Auric__ wrote :
> GS wrote:
>
>> Lots of issues with this!
>> Firstly, you need to ask your Q in an Excel group. This group is for 
>> Classic VB NOT Excel VBA!
>> 
>> Secondly, obviously your code is a rework of using the most inefficient 
>> macro recorder.
>> 
>> Thirdly, none of your variables are declared.
>> 
>> Fourthly, there's way, way, way too much unnecessary range selecting 
>> going on, making for rather poor performance.
>
> Gee, Garry, tell us how you *really* feel. ;-)

Sorry about that! I have a really nasty pet peeve about how much junk 
code Excel's macro recorder generates!<g> I'd be happy to 'clean it up' 
IF the OP posts to an appropriate NG I subscribe to...

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.basic.visual.misc

csiph-web