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

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

From	"blank" <blank@blankety.blank.com>
Newsgroups	comp.lang.basic.visual.misc
References	(1 earlier) <jgrons$jbg$1@dont-email.me> <4f3168b9$1@dnews.tpgi.com.au> <jgrvrl$1sq$1@dont-email.me> <4f3184ec@dnews.tpgi.com.au> <jgs36i$t78$1@dont-email.me>
Subject	Re: I'm getting run time error 1004 and I need help editing my code
Date	2012-02-08 10:48 +1100
Message-ID	<4f31b845$1@dnews.tpgi.com.au> (permalink)

Show all headers | View raw

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

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