![]() |
|
|
|
#1
|
||||
|
||||
|
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
|
|
#2
|
|||
|
|||
|
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
|
|
#3
|
||||
|
||||
|
Not one cell at a time:
|
|
#4
|
|||
|
|||
|
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. |
|
#5
|
||||
|
||||
|
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!
|
|
#6
|
|||
|
|||
|
Thanks a lot...Great piece of code...Happy Knowledge Sharing...
|
|
|
|
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 |