data type mismatch error

[Daniel]

Below is my macro to draw bar chart Pattern DarkUpward Diagonal if
Rng2.value in data sheet >3. Today, there are no data and I have this error
'data type mismatch'

How can I fix this

Thanks In advance
Daniel



Dim Cnt2 As Integer
Dim S As Integer
r = Range("B1").CurrentRegion.Rows.Count


For Each Rng2 In Range(.Cells(2, 6), .Cells(r, 6))
Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt2)

Pts.HasDataLabel = True


S = Now - Rng2.Value

Pts.DataLabel.Font.Size = 7

Pts.DataLabel.Text = S


If Now - Rng2.Value > 3 Then

Pts.Fill.Patterned Pattern:=msoPatternDarkUpwardDiagonal


End If
Cnt2 = Cnt2 + 1
Next Rng2

 

[JLGWhiz]

Use a qualification statement like: If Rng2.Value > 0 Then 'do something

I would try to pick a point in my code where the test would have to be done
only once, instead of testing each iteration.

 

[JLGWhiz]

The way your code is structured, you will have to include the test within the
For...Next statement as shown below:

Dim Cnt2 As Integer
Dim S As Integer
r = Range("B1").CurrentRegion.Rows.Count
For Each Rng2 In Range(.Cells(2, 6), .Cells(r, 6))
If Rng2 <> "" Then '<<<Test for value in Rng2
Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt2)
Pts.HasDataLabel = True
S = Now - Rng2.Value
Pts.DataLabel.Font.Size = 7
Pts.DataLabel.Text = S
If Now - Rng2.Value > 3 Then
Pts.Fill.Patterned Pattern:=msoPatternDarkUpwardDiagonal
End If
Cnt2 = Cnt2 + 1
End If
Next Rng2

 

[Daniel]

Thanks JLWhiz
I will include this test code in my macro.
I check and it works fine
Thanks again for taking time with valuable advice.
Daniel

 

[JLGWhiz]

One other thing you might try is to use the Format function to get your data
elements to the same data type. Assuming that Rng2 is a date from the way it
is used in the test If Now - Rng2.Value... , you could do something like this:

If Format(Now, "d") - Format(Rng2.Value, "d") > 3 Then
Pts.Fill.Patterned Pattern:=msoPatternDarkUpwardDiagonal
End If

The minus sign yields a numeric result so it should not give you the type
mismatch.
You can try both methods and see which gives the best results.