Groups > comp.lang.basic.visual.misc > #741

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

From	GS <gs@somewhere.net>
Newsgroups	comp.lang.basic.visual.misc
Subject	Re: I'm getting run time error 1004 and I need help editing my code
Date	2012-02-07 15:56 -0500
Organization	A noiseless patient Spider
Message-ID	<jgs36i$t78$1@dont-email.me> (permalink)
References	<528cb6d4-caa7-4fb9-9bfa-e0f903ed2107@w4g2000vbc.googlegroups.com> <jgrons$jbg$1@dont-email.me> <4f3168b9$1@dnews.tpgi.com.au> <jgrvrl$1sq$1@dont-email.me> <4f3184ec@dnews.tpgi.com.au>

Show all headers | View raw

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

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web