0

During the test of the code below, I was referring to a range as Saved.Range("C3:C66") and everything was working. Now I have to switch to cell index(Saved.Range(Cells(3, colIndex), Cells(66, colIndex))), as the column index is variable, but something is not working. Even if I use the indexes for the cells an error (runtime error 1004 vba method range of object _worksheet failed) appears. Any help is highly appreciated in advance.

   Sub WriteToWorksheetFromBackup(dict As Dictionary, sh As Worksheet)
    Dim cell As Range
    Dim IDnumber As Variant, objectID As clsDetector
    Dim row As Long
    row = 7
    
' Check keys in sheet Saved, against the dictionary and write on a sheet

    'For Each cell In Saved.Range("C3:C66")
    
    For Each cell In Saved.Range(Cells(3, 3), Cells(66, 3))
    
        IDnumber = cell.Value
        If dict.Exists(IDnumber) Then
            Set objectID = dict(IDnumber)
            With objectID
                sh.Cells(row, 1).Value = IDnumber
                sh.Cells(row, 2).Value = .DetectorType
                sh.Cells(row, 3).Value = .DetectorZone
                sh.Cells(row, 4).Value = .DetectorNo
                sh.Cells(row, 5).Value = .DetectorDeck
                sh.Cells(row, 6).Value = .DetectorLocation
                sh.Cells(row, 7).Value = .DetectorTestCount
                sh.Cells(row, 8).Value = .DetectorArea
                row = row + 1
            End With
        End If
    Next cell
End Sub
nikolayDudrenov
  • 159
  • 2
  • 9

0 Answers0