• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average Time and Count per day

S

Sameer.k21

Member
Hello,

I have trying to get average time taken and count of entries for the day.

The uf is created and the sample data is updated.

Please help me in correcting this code or if this is wrong, suggest me the right one.

With Application
Me.TextBox1.Value = .AverageIf(Sheet1.Columns("G"), Date)
Me.TextBox2.Value = .CountIf(Sheet1.Columns("C"), Date)
End With

Regards,
Sameer
 

Attachments

  • Sample Tracker1.xlsb
    28.4 KB · Views: 2
Hi Sameer ,

At what stage do you get the error ?

What data is to be entered in the userform so that this error appears ?

Narayan
 
Launch the uf and error says Type Mismatch.

For a change, I moved the code from userform_initialize to this, still I get the error

Code: 
Private Sub TextBox1_Enter()
With Application
    Param = Range("$C:$C").Address & "," & """" & Date & """" & "," & Range("$G:$G").Address
    Me.TextBox1.Value = Format(Application.Evaluate("=AVERAGEIF(" & Param & ")"), "hh:mm:ss")
   
   
    Param = Range("$C:$C").Address & "," & """" & Date & """"
    Me.TextBox2.Value = Format(Application.Evaluate("=COUNTIF(" & Param & ")"), "    0")
End With
End Sub
 
The said error occurred as XL recognised the said date in col C as text. multiply them with 1 & check.
 
AverageIf through an error when supplied criteria doesn't meet. While debugging it showed error 2007.
 
Hi Sameer ,

I don't get any error when I click on the Tracker Tool button ; the userform opens and displays 26 in the serial number combobox.

Narayan
 
I am not sure if its due to the version or format. But I still get the error.
I have 2013 MS Office version on Windows 7 PC.
 
A Small addition might resolve the issue!

Code: 
Private Sub UserForm_Initialize()
    Dim i As Double, v As Variant, r1 As Range
        v = Sheet1.Range("SINo."):    Me.sinum.list = v
            i = Application.Max(Sheet1.Columns(1)) + 1
        Me.sinum.Value = i
     
        Set r1 = Sheet1.Range("A1").CurrentRegion.Resize(, 1).Offset(, 2)
    r1.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
     
With Application
    Param = Range("$C:$C").Address & "," & """" & Date - 1 & """" & "," & Range("$G:$G").Address
    Me.TextBox1.Value = Format(Application.Evaluate("=AVERAGEIF(" & Param & ")"), "hh:mm:ss")
 
 
    Param = Range("$C:$C").Address & "," & """" & Date - 1 & """"
    Me.TextBox2.Value = Format(Application.Evaluate("=COUNTIF(" & Param & ")"), "    0")
End With

End Sub
 
You must log in or register to reply here.
Back
Top