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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Status
Not open for further replies.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,120
Messages
5,857,492
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top