|
|
#1
03-30-2019, 11:33 PM
|
|||
|
|||
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. 
|
|
#2
03-31-2019, 12:11 AM
|
||||
|
||||
|
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!
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
|
|
#3
03-31-2019, 01:14 PM
|
||||
|
||||
|
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
|
|
#4
03-31-2019, 11:32 PM
|
|||
|
|||
|
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.)
|
|
#5
04-01-2019, 01:34 AM
|
|||
|
|||
|
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
|
|
#6
04-01-2019, 05:22 AM
|
||||
|
||||
|
Not one cell at a time:
Quote:
|
|
#8
04-01-2019, 07:05 AM
|
|||
|
|||
|
Quote:
|
|
#9
04-01-2019, 08:12 AM
|
||||
|
||||
|
Quote:
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
 Can I make Word remember the window size and position for each document?
|
Jennifer Murphy | Word | 4 | 02-20-2023 06:36 PM |
| Creation of a multiple choose to create chapters | Xorre | Word VBA | 1 | 01-06-2017 01:39 AM |
|
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 |
Windows 10 
Office 2016
Linear Mode
