macro help needed

[ashishc]

Hi all,
I need to write a macro for the following case, can anyone help me with it?

I have an excel sheet which has 6 columns, one column is Status, 2nd column is time and third col is pid. Now for any case there can be many "sets" of data having the same pid, but different status and times.

The staus could be Start, doing 1, doing 2, error1, complete.

I need to write something which can add another column to this worksheet and for each pid, give me the difference between times where status is complete minus start.

Can someone explain me how to do this?

[kadghar]

Hi all,
...

Can someone explain me how to do this?

Its not that hard, you should give it a try first. Google how to use FOR/NEXT , DO/LOOP, CELLS, and some array's managing.

If you have some particular doubts, we'll be glad to give you a hand or point you in the right direction.

[ashishc]

Hi,
I did write something, but its not working, so if you could help me with it that will be great. I am posting my sample from excel here. This is really urgent so if you could post some code, that will be great.

Thanks


This is what my excel looks like

144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingJob 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteJob 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingJob 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingModule 23:56:29
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteModule 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteJob 23:57:53
144B7FC1-0761-4CC0-A112-91AFFDA08411 StartingJob 23:56:22
144B7FC1-0761-4CC0-A112-91AFFDA08411 CompleteJob 23:57:43

[kadghar]

Hi,
I did write something, but its not working, so if you could help me with it that will be great. I am posting my sample from excel here. This is really urgent so if you could post some code, that will be great.
Thanks
...

Well, it'd have helped if you had showed us what you did in VBA, instead of your excel's sheet. I'll write an example; for this i'll have ID in the first column, Status in second and Time/Date in third. This will look for the first ID and its Start and Complete status, Then it'll show you the difference in a msg box

sub kad1()
dim a
dim i as long
dim Dat1 as date: dim Dat2 as date
a = range(cells(1,1), cells(1,1).end(-4121))
for i= 1 to ubound(a)
    if a(i,1) = a(1,1) and a(i,2) = "Start" then Dat1 = a(i,3)
    if a(i,1) = a(1,1) and a(i,2) = "Complete" then Dat2 = a(i,3)
next
msgbox dat2-dat1
end sub

This will give you an idea.

HTH