Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Thank you so much Steve….
    You are very welcome. I am glad I could help.

    It would be really great, if I can get your mail Id please
    I prefer if you ask Excel questions on the board, or if unrelated to excel you can send me a PM to my username on this board.

    can i know what is your profession?
    I am a PhD chemist by education working to develop rigid foam insulation formulations.

    Can you please also tell me how you learned this technique of writing macros… after seeing you people in the forum, I am so eager to learn this and I also want to help others like you…
    I have learned by writing code on this and other boards to answer questions. I learned a lot of preliminary coding by using the macro recorder with simple tasks to understand Excel's object model and as mentioned before, stepping through various macros to watch what the lines are doing.

    I recommend that you step through the code and even perhaps examine and add comments to it.
    You can add comments by prefacing them with a single quote(') or adding a single quote and note at the end of the line:

    Code:
    'This is a comment on its own line
    i = i + 1
    x = x + 1 'this is a comment at the end of a line
    You can mark the various sections of code (I leave it to you to discover where to put these
    'Variable defining
    'Setting up sheets
    'Adding titles
    'Get Current Interval
    'List cols to the left of the timestamp
    'List timestamp
    'List cols to the right of timestamp
    'Go to next row in source
    'Get next interval
    'Go to next row in destination
    'format cells
    'autofit columns
    'Let user know code is done


    In my output, in the first two cols namely trench and sector…

    I get few empty cells. If I want to fill it up with that trench numbers and sectors numbers what I have to do?
    There are 4 sections on the output sheet
    1) the first row is the title
    2) this is the section with the cols to the left of the timestamp
    3) timestamp column
    4) cols to the right of the timestamp

    You are asking about section2 (left of the timestamp). But to understand when some are listed and others are not you must examine the code. You should be able to find all those sections in the code [If you added the previous cols, it should be done now]. Section (1) [Title] is printed once. Section 3=Timestamp is printed once for each row (it has no conditions).

    Sections 2(left) and 4(right) have conditions [The lines to print them are contained in an IF .. End if construct] which were set based on your example. Section4 is not printed when intervals are not part of the source (it fills in "missing" time elements so a time for each of the 12 time segments is listed.

    Section 2(left) [that you asked about] is not printed when the current interval (the interval being listed) is the same as the previous interval. This was per the example. It is in the code:

    Code:
          If iIntPrev <> i _
            And wDest.Cells(lRowDest - 1, 4) <> wDest.Cells(lRowDest, 4) _
            Or Not (bFilling) Then
            wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
            wDest.Cells(lRowDest, 2) = .Cells(lRowSource, 2)
            wDest.Cells(lRowDest, 3) = "'" & vArray(i)
            iIntPrev = i
          End If
    If you want them to be listed all the time (which is NOT what was in your example output), all you need do is move them out of the If.. End If. This will list the trench and sector every time, but not display a repeating Interval:

    Code:
          wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
          wDest.Cells(lRowDest, 2) = .Cells(lRowSource, 2)
    
          If iIntPrev <> i _
            And wDest.Cells(lRowDest - 1, 4) <> wDest.Cells(lRowDest, 4) _
            Or Not (bFilling) Then
            wDest.Cells(lRowDest, 3) = "'" & vArray(i)
            iIntPrev = i
          End If
    If you want all three to be listed, you can use:
    Code:
          wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
          wDest.Cells(lRowDest, 2) = .Cells(lRowSource, 2)
          wDest.Cells(lRowDest, 3) = "'" & vArray(i)
    But there would be some other code simplification since there would be no reason to even check for the previous interval at all since it would not matter....

    If you have conditions when it you want them to be listed and other times when they should not be, you would have to explain to me when they should and should not be listed. My understanding of the original output was that you did NOT want the cols to the left of the timestamp listed when the current interval was a duplicate of the previous interval...

    Hope this helps,
    Steve

  2. The Following User Says Thank You to sdckapr For This Useful Post:

    dharani suresh (2014-05-07)

  3. #17
    Lounger
    Join Date
    Apr 2014
    Posts
    25
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You are very welcome. I am glad I could help.

    Hope this helps,
    Steve
    Hello Steve,

    It was sooo nice to hear all these things from you… Being a chemist person, I wonder how you manage to do this macro codings… Definitely I will follow your suggestions to learn this technique..
    Thank you steve

    Regarding that trench and sectors, yes you are right I didn’t mention in my output model.. its my mistake.. I am sorry… I placed the following two lines outside the if and end if… then I am getting continuous numbers in trench and sectors cols… thank you steve…
    Code:
    wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
          wDest.Cells(lRowDest, 2) = .Cells(lRowSource, 2)
    but if I pace the third line (interval line)
    Code:
    wDest.Cells(lRowDest, 3) = "'" & vArray(i)
    Outside the loop then I am getting the continuous intervals but wrongly… which means…

    I need like this…
    B1 1 12-14 04/11/2014 13:37:00 16490.6895
    B1 1 14-16 04/11/2014 14:47:27 15973.4932
    B1 1 14-16 04/11/2014 15:21:52 15991.8086
    B1 1 14-16 04/11/2014 15:55:50 16736.2363
    B1 1 16-18 04/11/2014 16:29:26 16503.9902
    B1 1 16-18 04/11/2014 17:52:49 17390.0293
    B1 1 18-20 04/11/2014 19:52:55 17204.8086
    B1 1 20-22 04/11/2014 21:53:01 17059.5352

    But I am getting like this,
    B1 1 12-14 04/11/2014 13:37:00 16490.6895
    B1 1 14-16 04/11/2014 14:47:27 15973.4932
    B1 1 16-18 04/11/2014 15:21:52 15991.8086
    B1 1 16-18 04/11/2014 15:55:50 16736.2363
    B1 1 16-18 04/11/2014 16:29:26 16503.9902
    B1 1 18-20 04/11/2014 17:52:49 17390.0293
    B1 1 18-20 04/11/2014 19:52:55 17204.8086
    B1 1 20-22 04/11/2014 21:53:01 17059.5352

    Its accounting for the next intervals rather than taking previous intervals…
    What shall I do for this???

    Thank you steve..
    I don’t have to word to express my gratitude and tribute to you..

    With Kind Regards,
    Dharani.

  4. #18
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does changing it to:
    wDest.Cells(lRowDest, 3) = "'" & vArray(iInt)

    work?

    [This is a good time to point out that this demonstrates the reason for understanding and communicating the desired output initially. We are trying to modify to account for a redesign after the code was completed, and now the logic has changed...]

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    dharani suresh (2014-05-07)

  6. #19
    Lounger
    Join Date
    Apr 2014
    Posts
    25
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Does changing it to:
    wDest.Cells(lRowDest, 3) = "'" & vArray(iInt)

    work?

    [This is a good time to point out that this demonstrates the reason for understanding and communicating the desired output initially. We are trying to modify to account for a redesign after the code was completed, and now the logic has changed...]

    Steve
    Hello Steve,
    Thank you for this wonderful code and for this extra ordinary help...!!!
    My heartfelt thanks to you...

    I think you got irritated now... i am extremely sorry if i made you to feel irritated...
    My professor needs all these modifications that's why i asked you all these things later..

    you really helped me a lot... thank you so much... and also i have send you friend request... please accept that req...

    Thank you...

    With Kind Regards,
    Dharani.

  7. #20
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Thank you for this wonderful code and for this extra ordinary help...!!!
    My heartfelt thanks to you...
    Again, you are very welcome.

    I think you got irritated now... i am extremely sorry if i made you to feel irritated...
    No I am not irritated, I apologize if I came across that way. My point was to try and teach while there was a teaching moment. The code logic became overly complicated since you had the initial requirement to leave blank spaces to the left, which was changed later. My point was to ensure that you know the requirements up front and even understand where you may be modifying to try and build in logic for future modifications...

    My professor needs all these modifications that's why i asked you all these things later..
    It doesn't matter who needs them, it was about ensuring you know the requirements up front, especially if you are going to ask others for help...

    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    dharani suresh (2014-05-08)

  9. #21
    Lounger
    Join Date
    Apr 2014
    Posts
    25
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Again, you are very welcome.



    No I am not irritated, I apologize if I came across that way. My point was to try and teach while there was a teaching moment. The code logic became overly complicated since you had the initial requirement to leave blank spaces to the left, which was changed later. My point was to ensure that you know the requirements up front and even understand where you may be modifying to try and build in logic for future modifications...



    It doesn't matter who needs them, it was about ensuring you know the requirements up front, especially if you are going to ask others for help...

    Steve

    THANK YOU STEVE... I WILL DEFINITELY KEEP THIS IN MY MIND IN FUTURE WHILE ASKING SUCH HELP...

    Dharani.

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •