Run time error appears on 7th Iteration of code

[orangebloss]

Hi

My code works fine for the first 6 iterations but then throws a Run-Time error '91': Object variable or With block variable not set and I'm lost as to why! Any thoughts on the cause appreciated

Sub Updateconfig()

       Dim rngData As Range, cell As Range, rngStart As Range
       Dim Profile As Worksheet, Configtotal As Worksheet
       Dim Configtotaltable As ListObject
       Dim Count As Integer, foundcolumn As Integer, pastecolumn As Integer
      
      
      
      
     
       Set Profile = Worksheets("Profiles")
       Set Configtotal = Worksheets("Config totals")
       Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)
       Set rngStart = ActiveSheet.Range("F6")
       Count = 0
      
      
       Set Configtotaltable = Configtotal.ListObjects("Config_Dates")
       Configtotal.ListObjects("Config_Dates").Range.AutoFilter Field:=3
      '  Configtotal.Select
      Range("H6:AC300").Clear
 
      ActiveSheet.Range("F6").Select
     
     
  
 With Configtotal
        Set rngData = .Range(.Range("E6"), .Range("E" & .Rows.Count).End(xlUp))
      
       End With
   
      ' Establish "For" loop.
      For Each cell In rngData.Cells
         If cell.Value = "CME" Then
            'If the value in the first cell of the row is CME then
           
            CopyCMEData
             Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)
            ' MsgBox (foundCell)
            foundcolumn = foundCell.Column
            ' MsgBox (foundcolumn)
           
             pastecolumn = foundcolumn - 6
            ' MsgBox (pastecolumn)
             rngStart.Offset(Count, pastecolumn).PasteSpecial paste:=xlPasteValues
          Application.CutCopyMode = False
            Count = Count + 1
            rngStart.Offset(Count, 0).Select
        
         
          
Else
CopyNPDData
Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)
             Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)
             'MsgBox (foundCell)
             foundcolumn = foundCell.Column
               
             '   MsgBox (foundcolumn)
             pastecolumn = foundcolumn - 6
            
           '  MsgBox (pastecolumn)
             rngStart.Offset(Count, pastecolumn).PasteSpecial paste:=xlPasteValues
          Application.CutCopyMode = False
            Count = Count + 1
            rngStart.Offset(Count, 0).Select
          
    End If
    
      Next cell
End Sub

The code looks at the RStart date, finds the column that the date is in and then pastes predetermined values - this works fine until the 7th iteration when it throws a wobbly when it gets to this section of code:

  CopyCMEData
             Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)
            ' MsgBox (foundCell)
            foundcolumn = foundCell.Column

Lever

RStart

2022/01

2022/02

2022/03

2022/04

2022/05

2022/06

2022/07

2022/08

2022/09

2022/10

2022/11

NPD

2022/03

0.05882353​

0​

0.11764706​

0​

0.11764706​

0​

1.05882353​

0​

1.35294118​

CME

2022/05

0.05882353​

0​

0.02941176​

0​

0.02941176​

0​

0.70588235​

CME

2022/09

0.05882353​

0​

0.02941176​

CME

2022/10

0.05882353​

0​

CME

2022/10

0.05882353​

0​

CME

2022/11

0.05882353​

CME

2022/29

CME

2022/30

 

[rlv01]

Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)

That searches for a value using the range "Find" method. You have to handle the scenario where nothing is found. Example

    If foundCell Is Nothing Then
        MsgBox "'" & ActiveCell.Value & "' not found in range", vbOKOnly Or vbCritical, Application.Name
        Exit Sub
    End If

 

[orangebloss]

Set foundCell = ActiveSheet.Range("H:AC").Find(ActiveCell.Value, , xlValues, xlWhole)

That searches for a value using the range "Find" method. You have to handle the scenario where nothing is found. Example

    If foundCell Is Nothing Then
        MsgBox "'" & ActiveCell.Value & "' not found in range", vbOKOnly Or vbCritical, Application.Name
        Exit Sub
    End If

Thanks! I also found out that I hadn't extended the find range far enough!