CODEWITHSUNDEEP
vbaexcelobject
Miwiph
Miwiph

Reputation: 11

VBA Object variable or with block

I keep getting the

Object variable or with block variable not

set error even though I have defined all variables. I know there are plenty of other questions regarding this being asked here, however non of them seem to help me.

I am simply trying to update a number of graphs. I have used the code before in other workbooks, which is why i cannot find the error. 

Sub Update_All_Graphs()
    Call SETWorksheets

    Dim Lastrow As Long
    Dim ShName As String
    Dim FindRow, FindRowNumber As Double

    Set FindRow = ScTotalWs.Range("AF10:AF47").Find(What:="Grand Total", LookIn:=xlValues)
    FindRowNumber = FindRow.Row
    Lastrow = FindRowNumber - 1

    ScTotalWs.ChartObjects("1_EOST").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Range("AJ10:AJ" & Lastrow & ", AK10:AK" & Lastrow & ", AL10:AL" & Lastrow)
End Sub

The error shows up on the FindrowNumber = FindRow.row line.

Upvotes: 0

Views: 117

Answers (3)

user3598756
user3598756

Reputation: 29421

Find() method of Range object has a subtle trap in that it assumes for some parameters, if not explicitly declared, their last value, with last being their values used during the last use of that method, even if from Excel UI!

so you'd better always explicitly specify all those parameters and assure you're actually searching as you want it to be done:

Set FindRow = ScTotalWs.Range("AF10:AF47").Find(What:="Grand Total", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

but you could also avoid the setting and checking against Nothing of the Range object returned by the Range object Find() method by using WorksheetFunction.CountIf() function, as follows:

Sub Update_All_Graphs()
    Dim Lastrow As Long

    With ScTotalWs.Range("AF10:AF47") '<--| reference relevant range of wanted worksheet
        If WorksheetFunction.CountIf(.Cells, "Grand Total") > 0 Then '<--| if there's at least one occurrence of "Grand Total"
            Lastrow = .Find(What:="Grand Total", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False).row - 1 '<--| set your 'LastRow'
            With .Parent '<--| reference the parent worksheet of referenced range, for all subsequent ranges references
                .ChartObjects("1_EOST").Activate
                ActiveChart.SeriesCollection(1).Select
                ActiveChart.SetSourceData source:=.Range("AJ10:AJ" & Lastrow & ", AK10:AK" & Lastrow & ", AL10:AL" & Lastrow)
            End With
        End If
    End With
End Sub

Upvotes: 1

Tomalak
Tomalak

Reputation: 338248

The Range.Find() method is not guaranteed to return anything (logical, if you think about it). The documentation states clearly:

Remarks

This method returns Nothing if no match is found.

Never use the result of such a method call without checking that it is valid.

Set FindRow = ScTotalWs.Range("AF10:AF47").Find(What:="Grand Total", LookIn:=xlValues)

If Not FindRow Is Nothing Then
    Lastrow = FindRow.Row - 1
    ' etc
End If

Upvotes: 1

snibbo
snibbo

Reputation: 189

Probably, you need to define "ScTotalWs".

Set ScTotalWs = ThisWorkbook.Worksheets("Table1")

Of course, you need to Change [Table1] according to your file.

Upvotes: 0

Related Questions