Code with error

Status
Not open for further replies.

ojackiec

New Member
Joined
Dec 5, 2005
Messages
17
OK. I have a nother posting on here but thought I would bring this up separately. I have a chunk of code that is giving me an error message. I've only been doing this for a little over a month so I'm not too handy yet. I know the code is messy but here is the problem:
When it is run, I get "Run-time error: 438 Object doesn't support this property or method" for the following lines of code

Code:
ToSheet.Cells(ToRow, 1).Value _
            FromSheet.Cells(FromRow, 1).Value

Here is the chunk of code so that you see where it fits in:

Code:
Sub LeakLog()
'
' LeakLog Macro
'
'

'
   Dim ws As Worksheet
    
    Set ws = Worksheets.Add()
    ws.Name = "Leak Log"
    
    ActiveCell.FormulaR1C1 = "Component"
    Range("A1:C1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Selection.Font.Bold = True
    Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = "COMPONENT"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "FIELD DATA"
    Range("D1:F1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Selection.Font.Bold = True
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Tag No."
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "LDAR Action"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Value (ppm)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Date"
    Rows("2:2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Range("A1:F1").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Columns("B:B").ColumnWidth = 27
    Range("A2:F2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "REPAIR COMMENTS"
    With ActiveCell.Characters(Start:=1, Length:=15).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("G2:H2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    Range("G2:H2").Select
    Range("A1:C1").Select
    
    Columns("D:D").ColumnWidth = 12
    Columns("D:D").Select
    Range("D2").Activate
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Columns("E:E").ColumnWidth = 10.71
    Columns("F:F").ColumnWidth = 13
    Columns("F:F").Select
    Range("F2").Activate
    Selection.NumberFormat = "m/d/yyyy"
    Columns("G:G").ColumnWidth = 17.71
    Columns("I:I").ColumnWidth = 17.29
    Columns("H:H").ColumnWidth = 26.29
    Range("G2:H2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With

'Find records from Field Log and copy to Leak Log
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim ToSheet As Worksheet
Dim ToRow As Long
Dim FindThis As Variant
Dim FoundCell As Object

Application.Calculation = xlCalculationManual
Set FromSheet = ThisWorkbook.Worksheets("Log Sheet")
Set ToSheet = ThisWorkbook.Worksheets("Leak Log")
ToRow = 3

FindThis = InputBox("Please enter lowest screening value to find: ")
If FindThis = "" Then End

With FromSheet.Cells
    Set FoundCell = .Find(FindThis, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        'FirstAddress = FoundCell.Address
        FromRow = FoundCell.Row
    Do
        ToSheet.Cells(ToRow, 1).Value _
            FromSheet.Cells(FromRow, 1).Value
        ToSheet.Cells(ToRow, 2).Value _
            FromSheet.Cells(FromRow, 5).Value
        ToSheet.Cells(ToRow, 3).Value _
            FromSheet.Cells(FromRow, 4).Value
        ToSheet.Cells(ToRow, 5).Value _
            FromSheet.Cells(FromRow, 10).Value
        ToSheet.Cells(ToRow, 6).Value _
            FromSheet.Cells(FromRow, 11).Value
        
        Names.Add "SCREEN: ", "=D & ToRow"
        Names.Add "REPAIR METHOD: ", "=G & ToRow"
        ToRow = ToRow + 1
        
        Names.Add "REPAIR: ", "=D & ToRow"
        Names.Add "DELAY REASON: ", "=G & ToRow"
        ToRow = ToRow + 1
        
        Names.Add "RESCREEN: ", "=D & ToRow"
        Names.Add "SHUTDOWN: ", "=G & ToRow"
        ToRow = ToRow + 1
        
        Set FoundCell = .FindNext(FoundCell)
    Loop While Not FoundCell Is Nothing 'And _
        'FoundCell.Address <> FirstAddress
    
    End If
End With

MsgBox ("Done.")
    
End Sub

All help is greatly appreciated!!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,955
Members
412,300
Latest member
Chaneycr
Top