Check if cell next to found value is empty VBA

Question

I am totally new to VBA. I use it maybe once a year and when I do I search this and other forums for code. For my humble needs, that usually works perfectly. Unfortunately due to my lack of real understanding of the code, in this case I am unable to see what I`m doing wrong. So hopefully somebody can help.

I have a timesheet where I want to copy the values to a datasheet. I have that working. But it is important that the users fill-out the right weeknumber in the form. In order to check if they have done that, I have written a piece of code that should return a messagebox when the weeknumber is not filled. When I simply use a reference to a single cell the code works. But as the weeknumber is not filled in in the same cell on all forms I want the macro to search for the text "Weeknumber: " and then check if the cell next to it is empty or not.

Sub Weeknumber()
dim cl as Range
Worksheets("Invul_Tabel").Activate
With Worksheets("Invul_Tabel").Cells
Set cl = .Find("Weeknummer:", After:=.Range("A1"), LookIn:=xlValues)
If IsEmpty(Range(cl).Offset(0, 1).Value) = True Then
  MsgBox "Geen weeknummer ingevuld"
  Exit Sub
     End If
End With

End Sub

The code gives an error on the "If IsEmpty" line" (error 1004). Is probably simple, but i can`t seem to figure it out!

Excelosaurus · Accepted Answer · 2017-10-25T15:30:15.940

3

Your cl variable is already a range.

Change

If IsEmpty(Range(cl).Offset(0, 1).Value) = True Then

to

If IsEmpty(cl.Offset(0, 1).Value) Then

edited Oct 25 '17 at 15:30

answered Oct 25 '17 at 15:20

Excelosaurus

2,789
1
14
20

It'll work even without the `.Value` attribute, right? – Oct 25 '17 at 15:21
indeed. But I would remove the `.value` – iDevlop Oct 25 '17 at 15:24
2

It's better practice to explicitly put `.Value` because IsEmpty doesn't expect a Range object. Without `.Value`, you are relying on VBA to pass the default property of the Range class to IsEmpty. – Excelosaurus Oct 25 '17 at 15:25
Wow, didn't know that – Oct 25 '17 at 15:31
@Excelosaurus: .Value2 is preferred over .Value, as it is a bit faster – AlexM Oct 25 '17 at 19:02

Check if cell next to found value is empty VBA

1 Answers1