data type mismatch error
 

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

data type mismatch error
 

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.

"Daniel" wrote:

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

data type mismatch error
 

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" wrote:

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

data type mismatch error
 

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" wrote:

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" wrote:

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

data type mismatch error
 

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.

"Daniel" wrote:

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" wrote:

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" wrote:

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