0

I have a wokrbook with muliple sheets.

On my second sheet I have a little macro which works pretty good, but if want to run it, while i'm on a other sheet, excel vba shows me a bug and its not working.

This is my code:

Dim myDate As Variant, rng As Range 
Dim lastRow As Integer, penultimateRow As Integer
    
Set rng = Range("A1:A207") 
Set myDate = rng.Find(What:=Int(Date), LookIn:=xlFormulas)  

Worksheets("Lernzeit-Tracking").Cells(myDate.Row, myDate.Column + 1).Value = _
   Worksheets("Übersicht").Range("L20").Value 
    
With Worksheets("Lernzeit-Tracking") 
    penultimateRow = .Cells(.Cells(Rows.Count, 2).End(xlUp).Row, 2).End(xlUp).Row
    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
End With
    
If IsEmpty(Sheets("Lernzeit-Tracking").Cells(lastRow - 1, 2)) = True Then
    Sheets("Lernzeit-Tracking").Range(Cells(penultimateRow + 1, 2), Cells(lastRow - 1, 2)).Value = 0 
End If

The Error message says:

"Object variable not set"

and the line

Worksheets("Lernzeit-Tracking").Cells(myDate.Row, myDate.Column + 1).Value == Worksheets("Übersicht").Range("L20").Value

is marked

How can i run my macro while wokring on a other sheet?

braX
  • 11,506
  • 5
  • 20
  • 33
The Alchemist
  • 9
  • That line includes unqualified range references. Qualify them by extending the `With` block – chris neilsen Dec 26 '21 at 01:55
  • Also, when you use `.Find` you need to check the results after it to make sure it doesn't return `Nothing` before you can use it. If `myDate` is nothing, you cannot use it like you use it. – braX Dec 26 '21 at 02:15

1 Answers1

-1

You are missing an object qualifier on the following line

Set rng = Range("A1:A207")

Using Range without an object qualifier will return the range "A1:A207" on the activesheet. Instead try the below line or adjust the sheet name accordingly.

Set rng = Thisworkbook.Worksheets("Lernzeit-Tracking").Range("A1:A207")
Caleb
  • 7
  • 3
  • Thanks for your answer. It works now! But i get a new bug, when i want to run the macro while im working on a other sheet. The new error marked the penultimate line Sheets("Lernzeit-Tracking").Range(Cells(penultimateRow + 1, 2), Cells(lastRow - 1, 2)).Value = 0 . The only thing what the line does is to fill all the empy cells with a zero and it works while im working on the main sheet, but if im on other it shows me a error. Do you maybe got a solution for that? – The Alchemist Dec 29 '21 at 00:07
  • Sorry, I should have caught that. Similar to the first error, the Cells references in that line also need to be qualified, otherwise it is a reference to Activesheet.Cells. You can place that line within another With block like you did before and append a . before Cells. Alternatively, it will be very unreadable but you can replace the Cells references with Sheets("Lernzeit-Tracking").Cells . Hope that helps. – Caleb Dec 30 '21 at 01:06