Type Mismatch For One Sheet

[mouse88]

I am using the below code. If I use it when the report sheet is selected it runs fine but if I run it when the summary sheet is selected I get the type mismatch error.

Can anyone see something im missing?

Public Sub GetData()
Call GetRanges
Dim X As Integer
Dim Y As Integer
X = 0
Y = 0
For X = 1 To UBound(AgentData)
    For Y = AgentData(X).RowStartIndex To AgentData(X).RowEndIndex
    
        If Worksheets("Report").Range("D" & Y).Text <> vbNullString Then
    
            Select Case Worksheets("Report").Range("D" & Y).Text
            
                Dim Rng As Range
            
                Case "Not_Ready_Default_Reason_Code"
                Set Rng = Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 1)
                Rng.Value = TimeValue(Rng.Text) + TimeValue(Range("D" & Y).Offset(0, 2).Text)
                                
                Case "Other Admin"
                Set Rng = Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 2)
                Rng.Value = TimeValue(Rng.Text) + TimeValue(Range("D" & Y).Offset(0, 2).Text)
                                
                Case "Personal"
                Set Rng = Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 3)
                Rng.Value = TimeValue(Rng.Text) + TimeValue(Range("D" & Y).Offset(0, 2).Text)
                            
            End Select
    
        End If
    
    Next Y
Next X

 

[VoG]

Try

Public Sub GetData()
Call GetRanges
Dim X As Integer
Dim Y As Integer
X = 0
Y = 0
With Worksheets("Report")
    For X = 1 To UBound(AgentData)
        For Y = AgentData(X).RowStartIndex To AgentData(X).RowEndIndex
        
            If .Range("D" & Y).Text <> vbNullString Then
        
                Select Case .Range("D" & Y).Text
                
                    Dim Rng As Range
                
                    Case "Not_Ready_Default_Reason_Code"
                    Set Rng = .Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 1)
                    Rng.Value = TimeValue(Rng.Text) + TimeValue(.Range("D" & Y).Offset(0, 2).Text)
                                    
                    Case "Other Admin"
                    Set Rng = .Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 2)
                    Rng.Value = TimeValue(Rng.Text) + TimeValue(.Range("D" & Y).Offset(0, 2).Text)
                                    
                    Case "Personal"
                    Set Rng = .Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 3)
                    Rng.Value = TimeValue(Rng.Text) + TimeValue(.Range("D" & Y).Offset(0, 2).Text)
                                
                End Select
        
            End If
        
        Next Y
    Next X
End With

 

[mouse88]

That worked ok. Thanks.

For some reason the code is not calculating the values correctly.

The values in the report are formatted as general and in my summary table they are formatted as Time (hh:mm:ss).

Can you see anything jumping out in the code.

I have used Debug.Print to check it is looking up all the correct values and it is but somewhere along the route it is picking up extra data.

I have attached a copy of the workbook.

Thanks

http://www.megaupload.com/?d=RN2KY8B0

 

[VoG]

Maybe you need to format the results, like this

                    Case "Not_Ready_Default_Reason_Code"
                    Set Rng = .Range("SummaryRange").Find(AgentData(X).Name).Offset(0, 1)
                    Rng.NumberFormat = "hh:mm"
                    Rng.Value = TimeValue(Rng.Text) + TimeValue(.Range("D" & Y).Offset(0, 2).Text)

 

[mouse88]

Still doesn't add up right. The very first calculation is fine but when it moves on to the next one it is wrong.

 

[VoG]

If the values that you are looking at are times rather than text then I don't think that you need TimeValue()

 

[mouse88]

[SOLVED] Type Mismatch For One Sheet

Sorry ive just checked and they are calculating right. I was trying to add time up in my head and I was wrong.

Thanks for all your help.