Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Type Mismatch Error when getting data from another workbook | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |