macro help needed

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ashishc
    New Member
    • Jan 2008
    • 12
    #1

    macro help needed

    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?
    Tags: None
    • kadghar
      Recognized Expert Top Contributor
      • Apr 2007
      • 1302
      #2
      Originally posted by ashishc
      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.

        Comment

        • ashishc
          New Member
          • Jan 2008
          • 12
          #3
          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

            Comment

            • kadghar
              Recognized Expert Top Contributor
              • Apr 2007
              • 1302
              #4
              Originally posted by 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
              ...
              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

              [CODE=vb]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[/CODE]

              This will give you an idea.

              HTH

                Comment

                Previous template Next
                Working...