Run time error 91, Object variable or with block variable not set, lastrow

Question

Issue: I am getting a Run Time Error 91 "object variable or with block variable not set on Lastrowdata = [AB:AB].Find("*", , xlValues, , xlByRows, xlPrevious).Row in the below code

Full Code:

Sub test()
Dim Sht As Worksheet
Dim rng As Range, rngChart As Range, XLabelrng As Range
Dim Lastrowdata As Long, x As Long
Dim cht As Object
Dim myOrder As Variant

'Create Graph Data: Make Local Mgmt Details Short names
'Create graph
'Size Graph
For Each Sht In Worksheets
        
        'Local Management Details Mapping (graph data)
        With Sht
            .Range("AA8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",""FX""),"""")"
            .Range("AB8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)),"""")"
            .Range("AA9:AA18") = "=IF(E9=""Yield Curve"",""YC"",IF(E9=""Asset Allocation"",""A. Alloc"",IF(E9=""Security Selection"",""Sec Sel"",IF(E9=""Leverage"",""Lev"",IF(E9=""Intra-Day"",""Intra"",IF(E9=""Pricing Differences"",""Pric"",IF(E9=""Exclusions"",""Exc"",IF(E9=""Interest Rate Derivative Basis"",""IRD"",IF(E9=""Implied Volatility"",""Vol"",IF(E9=""Mortgage"",""Mtg"",IF(E9=""Residual"",""Res"",IF(E9=""Others"",""Others"",""""))))))))))))"
            .Range("AB9:AB18") = "=IF(I9="""","""",I9)"
            '.Range("AA8:AB18").Font.Color = vbWhite
        End With
        
        'Your data range and x-axis labels for the chart
        Lastrowdata = [AB:AB].Find("*", , xlValues, , xlByRows, xlPrevious).Row
        Set rng = Sht.Range("AB8:AB" & Lastrowdata)
        
        'Chart Location
        Set rngChart = Range("K9:W18")
    
        'Create a chart               (style,XlChartType,Left,Top,Width,Height,NewLayout)
        Set cht = Sht.Shapes.AddChart2(203, xlColumnClustered, 1, 1, 1, 1, False)
    
        'Chart setup
        With cht.Chart
          .SetSourceData Source:=rng
          .SeriesCollection(1).XValues = "='" & Sht.Name & "'!$AA$8:$AA" & Lastrowdata
          .HasTitle = False
          .HasLegend = False
          .Axes(xlValue).MajorUnit = 50
        End With
    
        'Chart location
        With cht
          .Left = rngChart.Left
          .Top = rngChart.Top
          .Width = rngChart.Width
          .Height = rngChart.Height
        End With
        
    
Next Sht

End Sub

Explanation of full code: This code puts formulas in columns AA-AB to use for x axis labels and graph data, then creates a graph. It loops through all sheets in the wb and does this for each sht. What is odd is when i run it once, it hits this runtime error as described above, at that point it has only put data in AA-AB in one sheet. If I then delete the formulas in AA-AB that it created and run it again, it runs perfectly in all sheets.

On top of that, the `[AB:AB]` and `Range("K9:W18")` resolve against either the containing sheet or the active sheet, depending on where this code resides. Qualify them with exact sheets like you have done everywhere else. — GSerg, Sep 19 '20 at 16:29
@gserg Thanks, you said "on top of that"... is there another comment that didnt save? — Ryan S, Sep 19 '20 at 18:54

Run time error 91, Object variable or with block variable not set, lastrow

0 Answers0