Error when using multiple cells in a Range command.

[ayman110229]

Hi,
I'm new to VBA and still learning. I'm trying to use VBA to create alar Pop-ups for Vaccination Due Dates but whenever I use a range of cells the macro wont run from some reason. Here's what I mean:

Option Explicit
Sub Due_Date()
Dim DueDate_Col As Range
Dim Due As Range
Dim PopUp_Notification As String
Set DueDate_Col = Range("D9:D54")
For Each Due In DueDate_Col
If Due <> "" And Date >= Due - Range("F9:F54") Then
PopUp_Notification = PopUp_Notification & " " & Due.Offset(0, -2)
End If
Next Due
If PopUp_Notification = "" Then
MsgBox "No Vaccinations are Due."
Else: MsgBox "These Dogs Need to be Vaccinated: " & PopUp_Notification
End If
End Sub

The bold underlined part always comes back with an error. But it works only if I choose 1 cell for example "F9" how can I fix this.

Thank you

 

[Joe4]

Welcome to the Board!

I am afriad that your code does not seem to make much sense to me, specifically this part:

Date >= Due - [B][U]Range("F9:F54")[/U][/B][U]

[/U]
Can you explian exactly what is in cell F9:F54, and what exactly you are trying to do there?

 

[ayman110229]

Welcome to the Board!

I am afriad that your code does not seem to make much sense to me, specifically this part:

Date >= Due - [B][U]Range("F9:F54")[/U][/B][U]

[/U]
Can you explian exactly what is in cell F9:F54, and what exactly you are trying to do there?

The range F9:F54 contains the value of how many days passed from the vaccination due date.

 

[ayman110229]

The range F9:F54 contains the value of how many days passed from the vaccination due date.

Book1.xlsx
	B	C	D	E	F	G
6	Worm Treatments
7	Dog		Next Due		Days Due
8
9	Aros		26/08/2022		5
10
11	Bara		31/08/2022		0
12
13	Ben		26/09/2022		-26
14
15	Bengo		26/09/2022		-26
16
17	Cheetah		26/09/2022		-26
18
19	Cizer		26/09/2022		-26
20
21	Dusty		26/09/2022		-26
22
23	Dzurko		26/09/2022		-26
24
25	Jacksy		26/09/2022		-26
26
27	Juliet		26/09/2022		-26
28
29	Leetah		26/09/2022		-26
30
31	Linda		26/09/2022		-26
32
33	Max		26/09/2022		-26
34
35	Naomi		26/09/2022		-26
36
37	Oakley		26/09/2022		-26
38
39	Oliver		26/09/2022		-26
40
41	Rony		26/09/2022		-26
42
43	Santos		26/09/2022		-26
44
45	Simba		26/08/2022		5
46
47	Toby		26/09/2022		-26
48
49	Twicksy		26/08/2022		5
50
51	Twister		27/08/2022		4
52
53	Yogi		26/09/2022		-26
54
Worming Due Dates

Cell Formulas
Range	Formula
F9,F53,F51,F49,F47,F45,F43,F41,F39,F37,F35,F33,F31,F29,F27,F25,F23,F21,F19,F17,F15,F13,F11	F9	=TODAY()-D9

 

[ayman110229]

Welcome to the Board!

I am afriad that your code does not seem to make much sense to me, specifically this part:

Date >= Due - [B][U]Range("F9:F54")[/U][/B][U]

[/U]
Can you explian exactly what is in cell F9:F54, and what exactly you are trying to do there?

Book1.xlsx
	B	C	D	E	F	G
6	Worm Treatments
7	Dog		Next Due		Days Due
8
9	Aros		26/08/2022		5
10
11	Bara		31/08/2022		0
12
13	Ben		26/09/2022		-26
14
15	Bengo		26/09/2022		-26
16
17	Cheetah		26/09/2022		-26
18
19	Cizer		26/09/2022		-26
20
21	Dusty		26/09/2022		-26
22
23	Dzurko		26/09/2022		-26
24
25	Jacksy		26/09/2022		-26
26
27	Juliet		26/09/2022		-26
28
29	Leetah		26/09/2022		-26
30
31	Linda		26/09/2022		-26
32
33	Max		26/09/2022		-26
34
35	Naomi		26/09/2022		-26
36
37	Oakley		26/09/2022		-26
38
39	Oliver		26/09/2022		-26
40
41	Rony		26/09/2022		-26
42
43	Santos		26/09/2022		-26
44
45	Simba		26/08/2022		5
46
47	Toby		26/09/2022		-26
48
49	Twicksy		26/08/2022		5
50
51	Twister		27/08/2022		4
52
53	Yogi		26/09/2022		-26
54
Worming Due Dates

Cell Formulas
Range	Formula
F9,F53,F51,F49,F47,F45,F43,F41,F39,F37,F35,F33,F31,F29,F27,F25,F23,F21,F19,F17,F15,F13,F11	F9	=TODAY()-D9

 

[Joe4]

OK, so each cycle should line up, so that each instance in D9:D54 looks at column F of that same row, right (i.e. D9 looks at F9, D10 looks at F10, D11 looks at F11, etc), right?
If so, change this line:

If Due <> "" And Date >= Due - Range("F9:F54") Then

to this:

If Due <> "" And Date >= Due - Due.Offset(0, 2) Then

And I highly, HIGHLY recommend NOT using merged cells... EVER!
They are probably the single worst feature of Excel and cause tons of issues, especially for things like VBA.

If merging cells across single rows, it is better to use the "Center Across Selection" formatting option shown here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[ayman110229]

OK, so each cycle should line up, so that each instance in D9:D54 looks at column F of that same row, right (i.e. D9 looks at F9, D10 looks at F10, D11 looks at F11, etc), right?
If so, change this line:

If Due <> "" And Date >= Due - Range("F9:F54") Then

to this:

If Due <> "" And Date >= Due - Due.Offset(0, 2) Then

And I highly, HIGHLY recommend NOT using merged cells... EVER!
They are probably the single worst feature of Excel and cause tons of issues, especially for things like VBA.

If merging cells across single rows, it is better to use the "Center Across Selection" formatting option shown here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Thank you sir this definitely solved and I'll check out that tutorial.

 

[Joe4]

You are welcome.

I hope my change makes sense.
You can use the OFFSET function to move from whatever cell you are on. We want to move two columns to the right to get to column F.