Microsoft Office Forums Choose multiple start and end position of window in a row...

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2019, 11:33 PM
Ganesh Kumar Ganesh Kumar is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Novice
Choose multiple start and end position of window in a row...
 
Join Date: Mar 2019
Posts: 3
Ganesh Kumar is on a distinguished road
Default Choose multiple start and end position of window in a row...

1 2 3 4 5 6 7 8 9 10
0 5 3 2 0 0 1 4 6 0

1. Start Time 1.End Time 2. Start Time 2. End Time



I need to scan non zero values in second row and corresponding first row value I need to achieve. Secondary, start of consecutive time slot and end of that time slot should reflect as below
In first start time I need to get 2 and end time as 4
Similarly for second start time I need to get 7 and end time as 9
This need to be updated automatically using formulas even the window vary its position and size can anyone help me with this thanks in advance
For this above example window is 2-4 and 7-9.

Last edited by Ganesh Kumar; 03-31-2019 at 01:55 AM.
Reply With Quote
  #2  
Old 03-31-2019, 12:11 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Choose multiple start and end position of window in a row... Windows 7 64bit Choose multiple start and end position of window in a row... Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 03-31-2019, 01:14 PM
p45cal p45cal is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

You could have left your attachment in, it would have been useful.

A user-defined function solution.
Array-enter it (use Ctrl+Shift+Enter, not just Enter, when committing the formula to the sheet). Do this with all 4 cells selected as you enter the formula. It's not limited to 4 cells, you can have 8 cells giving you additionally ST3, ET3, ST4, ET4, or as many as you want.

The formula in the attached is:
=blah(A1:J2)

The code for the udf is (and I'm sure it could be shorter):
Code:
Function blah(myRng)
Dim zz()
yy = myRng.Value
ReDim zz(1 To UBound(yy, 2) + 1)
idx = 1
For c = LBound(yy, 2) To UBound(yy, 2)
  Select Case c
    Case 1
      If yy(2, c) <> 0 Then
        zz(idx) = yy(1, c)
        idx = idx + 1
        If yy(2, c + 1) = 0 Then
          zz(idx) = yy(1, c)
          idx = idx + 1
        End If
      End If
    Case UBound(yy, 2)
      If yy(2, c) <> 0 And yy(2, c - 1) = 0 Then
        zz(idx) = yy(1, c)
        idx = idx + 1
      End If
      If yy(2, c) <> 0 Then
        zz(idx) = yy(1, c)
        idx = idx + 1
      End If
    Case Else
      If yy(2, c) <> 0 Then
        If yy(2, c - 1) = 0 Then
          zz(idx) = yy(1, c)
          idx = idx + 1
        End If
        If yy(2, c + 1) = 0 Then
          zz(idx) = yy(1, c)
          idx = idx + 1
        End If
      End If
  End Select
Next c
For i = idx To UBound(zz)
  zz(i) = "-"
Next i
blah = zz
End Function
Attached Files
File Type: xlsm msofficeforums42102.xlsm (16.4 KB, 14 views)
Reply With Quote
  #4  
Old 03-31-2019, 11:32 PM
ArviLaanemets ArviLaanemets is offline Choose multiple start and end position of window in a row... Windows 8 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: May 2017
Posts: 455
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

You current setup makes it very difficult to make any calculations, and nearly impossible automatic expansion of calculations when new data or new windows are added.

In attachment is an example, how I would make it (when I correctly assumed what you want to do).
(Columns with beige background may be hidden.)
Attached Files
File Type: xlsx TimeWindows.xlsx (14.0 KB, 3 views)
Reply With Quote
  #5  
Old 04-01-2019, 01:34 AM
Ganesh Kumar Ganesh Kumar is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Novice
Choose multiple start and end position of window in a row...
 
Join Date: Mar 2019
Posts: 3
Ganesh Kumar is on a distinguished road
Default

I did same as what you have suggested but i am not able to obtain it, I have attached the file for your reference. In what part i am failing kindly point out me. Thanks
Attached Files
File Type: xlsm Choosing Time.xlsm (15.8 KB, 2 views)
Reply With Quote
  #6  
Old 04-01-2019, 05:22 AM
p45cal p45cal is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Not one cell at a time:

Quote:
Originally Posted by p45cal View Post
Array-enter it (use Ctrl+Shift+Enter, not just Enter, when committing the formula to the sheet). Do this with all 4 cells selected as you enter the formula.
Reply With Quote
  #7  
Old 04-01-2019, 06:58 AM
xor xor is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,028
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I hope p45cal will accept that I make use of his formula in #3.

If so and if you can accept some helper cells the following might be of some interest.
Attached Files
File Type: xlsm Choosing Time_2.xlsm (16.2 KB, 4 views)
Reply With Quote
  #8  
Old 04-01-2019, 07:05 AM
Ganesh Kumar Ganesh Kumar is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Novice
Choose multiple start and end position of window in a row...
 
Join Date: Mar 2019
Posts: 3
Ganesh Kumar is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Not one cell at a time:
Thanks a lot...Great piece of code...Happy Knowledge Sharing...
Reply With Quote
  #9  
Old 04-01-2019, 08:12 AM
p45cal p45cal is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by xor View Post
I hope p45cal will accept that I make use of his formula in #3.
Where I struggled with the formula was it not detecting when the value in A2 was not zero. It looks like you've inherited that problem!
Reply With Quote
  #10  
Old 04-01-2019, 08:26 AM
xor xor is offline Choose multiple start and end position of window in a row... Windows 10 Choose multiple start and end position of window in a row... Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,028
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

@ p45cal

You are right. I overlooked that.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creation of a multiple choose to create chapters Xorre Word VBA 1 01-06-2017 01:39 AM
Choose multiple start and end position of window in a row... Can I make Word remember the window size and position for each document? Jennifer Murphy Word 1 12-18-2016 04:41 PM
Choose multiple start and end position of window in a row... Start from the previous position Dimsok Word VBA 7 09-14-2014 06:07 AM
Justify Paragraph Start position with VBA jeff_kaufman Word VBA 2 11-09-2013 12:15 PM
DL envelope window position rogerwilding Word 1 02-20-2013 01:57 PM


All times are GMT -7. The time now is 09:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft