Runtime error 1004: Method 'Range' of object '_Worksheet' failed occurs only if sheet is not active

[figment222]

I keep getting this error, but only when I'm on a page other than the page of the range object i'm setting.

Option Explicit


Public Sub LoadRecord()
    Dim Sheet3 As Worksheet
    Dim Sheet10 As Worksheet
    Dim SettingsCol As Long
    Dim FinalCol As Long
    Dim QuoteIDVal As Long
    Dim GageNumVal As Long
    Dim FeatNumVal As Long
    Dim RecordRow As Long
    Dim SettingsCount As Long
    Dim RecordRange As Range
    Dim Destination As Range
    Set Sheet3 = Worksheets("Records")
    Set Sheet10 = Worksheets("Parameters")
    
    
    'QuoteIDVal = Wizard.ComboBox2.Text
    'GageNumVal = Wizard.ComboBox3.Text
    'FeatNumVal = Wizard.ComboBox4.Text
    
    QuoteIDVal = 1030001
    GageNumVal = 1
    FeatNumVal = 1
    
    If MatchRecordRow(QuoteIDVal, GageNumVal, FeatNumVal, "B", "C", "D", "Records") = 0 Then
        MsgBox "Record Not Found"
        Exit Sub
    Else
        RecordRow = MatchRecordRow(QuoteIDVal, GageNumVal, FeatNumVal, "B", "C", "D", "Records")
        'MsgBox "Record is found in row: " & RecordRow
    End If
    
    SettingsCol = Sheet3.Range("1:1").Find("Settings").Column + 1
    FinalCol = Sheet3.Range("1:1").Find("Final").Column
    Set RecordRange = Sheet3.Range(Cells(RecordRow, SettingsCol), Cells(RecordRow, FinalCol))
    SettingsCount = FinalCol - SettingsCol
    MsgBox SettingsCount

This line towards the bottom is the one highlighted when the runtime error occurs:

Set RecordRange = Sheet3.Range(Cells(RecordRow, SettingsCol), Cells(RecordRow, FinalCol))

This code works, as long as I have Sheet3 displayed, but not if I am looking at sheet10. I have Option Explicit at the top of the module and I believe I have properly declared all variables. I know the references are ok, because it runs fine, as long as I am on sheet3, but I plan to have this sheet hidden and do not want to have it displayed when this code is run.

any ideas?

 

[Scott T]

The Cells need do not are not pointing to Sheet3 but to the active sheet.

Set RecordRange = Sheet3.Range([COLOR=#FF0000]Cells[/COLOR](RecordRow, SettingsCol), [COLOR=#FF0000]Cells[/COLOR](RecordRow, FinalCol))

try

Set RecordRange = Sheet3.Range(Sheet3.[COLOR=#FF0000]Cells[/COLOR](RecordRow, SettingsCol), Sheet3.[COLOR=#FF0000]Cells[/COLOR](RecordRow, FinalCol))

 

[NdNoviceHlp]

Set RecordRange = Sheet3.Range( Sheet3.Cells(RecordRow, SettingsCol),  Sheet3.Cells(RecordRow, FinalCol))

HTH. Dave

 

[figment222]

Bah! so simple! I thought I was covered because the variables I was using ("StartCol" & "FinalCol") were referencing sheet3, but my mistake was overlooking the fact that those are converting to a number and no longer reference the page, right?

    StartCol = Sheet3.Range("1:1").Find("Settings").Column + 1
    FinalCol = Sheet3.Range("1:1").Find("Final").Column

Because those are now only numbers to use as column references, I must reference again the correct sheet, like the example you gave:

   Set RecordRange = Sheet3.Range(Sheet3.Cells(RecordRow, StartCol), Sheet3.Cells(RecordRow, FinalCol))

Now, it works without having to activate sheet 3. Thank you both for the quick response!

 

[Scott T]

Unless told otherwise Range and Cells will point to the active sheet.


The With Sheets("Sheet1") makes .Cells(1,1) point to sheet1
Sheets("Sheet4").Cells(7, 7) tells the code that the sheet is Sheet4.
Because all the Cells are told what sheet to look what sheet is active does not matter.

With Worksheets("Sheet1")
    Sheets("Sheet4").Cells(7, 7) = .Cells(1, 1)
End With