Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-31-2019, 01:14 PM
p45cal's Avatar
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: 962
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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, 22 views)
Reply With Quote
  #2  
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, 10 views)
Reply With Quote
  #3  
Old 04-01-2019, 05:22 AM
p45cal's Avatar
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: 962
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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
  #4  
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,103
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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, 12 views)
Reply With Quote
  #5  
Old 04-01-2019, 08:12 AM
p45cal's Avatar
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: 962
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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
  #6  
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
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
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 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
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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:22 AM.


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