Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Looking for number of count result for all Rows

0

Hi,

I have applied the below code in the excel sheet, but it is giving result only for first 3 row and I am looking for results for rest row as well.

Please help me to resolve the error.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Union(Range("C4:C162"), Range("D4:D162"))) Is Nothing Or Target.Value <> True Then Exit Sub

Debug.Print Target.Address

Dim HypTime As Date

Dim StartTime As Date, EndTime As Date

' get time period for changes

StartTime = Range("F1").Value

EndTime = Range("F2").Value

'set criteria met time

HypTime = Format(Now(), "hh:mm")

If (HypTime >= StartTime) And (HypTime <= EndTime) Then

' if True added between start and end times, add 1 to Number of Counts and record time

If Target.Column = 3 Then

Cells(Target.Row, 6) = Cells(Target.Row, 6) + 1

Cells(Target.Row, 7) = HypTime

ElseIf Target.Column = 4 Then

Cells(Target.Row, 8) = Cells(Target.Row, 8) + 1

Cells(Target.Row, 9) = HypTime

End If

End If

End Sub

Attached sheets for your reference.

Regards,

Answer
Discuss

Answers

0
Selected Answer

Sunil

Only the first three rows work because of two things in the xlsm file:

  1. Sheet2's named range Source is  =Sheet2!$E$5:$G$7, needs to be =Sheet2!$E$5:$G$18 (to reflect the extended range in the Remote... xlsx file)
  2. You need to extend the range of the Worksheet_Change macro to reflect that, so:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("C4:C17"), Range("E4:E17"))) Is Nothing Or Target.Value <> True Then Exit Sub
'Debug.Print Target.Address  
That will populate the Report sheet when you change one of the cells beyond the third item in the Remote file (but it will also count a change for all of them initially).

Note too that you can comment out or delete the line 'Debug.Print Target.Address above (I left that in by accident before).

Don't forget to change your Workbook_Open()  sub which currently only covers the first three rows. Personally  I'd put today's date on your Report sheet and check if Date() equals that in Workbook_Open (and only clear if you haven't opened the file that day).     

Also, please don't assume everyone knows what happened before on your workbook- others would not know that both workbooks need to opened and changes made to the Remote... xlsx file.  Don't forget to enclose any code within the CODE tags (provided when you use the button named CODE).

Hope you can fix any minor issues beyond this Answer.

Discuss

Discussion

Thanks John, It works.
If possible, Can you able to help me with the coding to put today's date on Report sheet and check if Date() equals that in Workbook_Open (and only clear if you haven't opened the file that day).    
Thanks
SunilA (rep: 56) May 10, '21 at 8:30 am
Sunil

Once again you're extending the scope of your question (not quite fair in my opinion) and to something that should be within your capability- that's what I meant by "Hope you can fix any minor issues beyond this Answer" above.

I'll help you however... Suggest in the Report sheet you make A1 "Report Date" and set B1 to yesterday's date. Change the Open macro to:
Private Sub Workbook_Open()
 
Application.EnableEvents = False
With Worksheets("Report sheet")
        If .Range("B1") <> Date Then
            .Range("F4:J17").Clear
            .Range("B1") = Date
        End If
    End With
Application.EnableEvents = True
 
End Sub
Please review the range in bold above to clear the appropriate range (you seem to be setting a bigger offset so putting count and times in columns I and J). That however is your design so please don't ask me what to do with that!
John_Ru (rep: 6102) May 10, '21 at 9:09 am
I said yesterday's date so you can then close the workbook, re-open and check the clear cells (+ new report date) work.
John_Ru (rep: 6102) May 10, '21 at 9:10 am
Sunil. Please confirm that worked (or that you modified your approach) 
John_Ru (rep: 6102) May 11, '21 at 3:26 am
Sunil. Thanks for selecting my answer. Glad it worked. 
John_Ru (rep: 6102) May 19, '21 at 12:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login