Count the non blank cells in a row occurring after a ">0" value between two dates

[ashisha155]

I have been provided the daily sales data to track the post price revision performance of a product in different areas with different launch dates, now I've to calculate the no. of visits of the salesperson after the launch, that means I want to count the non blank cells occurring after the ">0" value in a row between two given dates:

Salesperson 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug LaunchDate Result
A 0 10 0 5 0 12 3-Aug 2
B 0 20 0 2-Aug 1
C 4 0 5 0 0 4-Aug 0
D 10 0 4 1-Aug 0

 

[ashisha155]

I-Aug was official launch date and the same was actual launch date with 10 units thus the 2 visits after that launch on 1-Aug should be counted as 2 only.

 

[venkat1926]

take the data I have already sent. enter "macro result" in J1 and color the cell as yellow.
after running macro the cells in column J will agree with your result in column I
only constraint is you have entered in H1 "launchdate" with no space between launch and date. keep it as it is. no unnecessary spaces no spelling mistake.

now run this macro "test"

Sub test()
Dim r As Range, c As Range, j As Long, cfind As Range, ldate As Range, k As Long
k = Rows("1:1").Cells.Find(what:="launchdate", lookat:=xlWhole).Column
Set r = Range(Range("H2"), Range("H2").End(xlDown))
For Each c In r
Set cfind = Rows("1:1").Find(what:=c.Value, lookat:=xlWhole)
If Cells(c.Row, cfind.Column) = 0 Then
Set ldate = Cells(c.Row, cfind.Column).Offset(0, 1)
Else
Set ldate = Cells(c.Row, cfind.Column)
End If
j = WorksheetFunction.CountIf(Range(ldate, Cells(c.Row, k - 1)), ">0")
'MsgBox j
Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = j
Next c
End Sub

Sub undo()
Range(Cells(2, Columns.Count).End(xlToLeft), Cells(2, Columns.Count).End(xlToLeft).End(xlDown)).Clear


End Sub

 

[ashisha155]

Hi Venkat

Thanks a lot it really works. Great.