CODEWITHSUNDEEP
excelvba
Mathlearner
Mathlearner

Reputation: 127

VBA offset from a particular cell

I have over 200 worksheets in a given workbook and am trying to get some prices based on given dates. I am trying to use the Find method to search column A in each sheet (apart from the Summary sheet) to find the given date and then offset 4 columns to get the appropriate prices. The code is failing on the fr = r.Offset(0, 4).Value and gives Object variable or With block variable not set error. I have tried tweaking the code and doing some other approaches, but keep getting a similar error.

Sub fill()
ActiveWorkbook.Worksheets("Summary").Activate

Dim From_Date As Variant
Dim To_Date As Variant

 From_Date = Range("A2").Value
 To_Date = Range("A1").Value

Dim rng As Worksheet
Dim fr As Variant
Dim tr As Variant
Dim pct As Variant
Dim r As Range

For Each rng In ActiveWorkbook.Worksheets
    If rng.Name <> "Summary" Then
        rng.Activate
        Set r = Range("A:A").Find(To_Date)
        fr = r.Offset(0, 4).Value
        
    End If
    

Next rng
End Sub

Upvotes: 0

Views: 167

Answers (1)

Dominique
Dominique

Reputation: 17565

You need to check the result of the Find() method, as explained here:

Set r = Range("A:A").Find(To_Date)
If Not (r Is Nothing) Then
  fr = r.Offset(0, 4).Value
  ...
End If

(Keep out not to use fr in case r is null, it might still contain the result of the previous loop)

Upvotes: 1

Related Questions