Text display problem

  • Thread starter Thread starter Mason Pratt
  • Start date Start date
M

Mason Pratt

I am currently using a function seen below to combine a text series of cells
(row or column) to display in one cell. This function works perfectly to
show me all the cells in the series. My question is: How do I modify the
function to skip every other cell (or skip every third cell, etc.) in the
text series and display the result in one cell? In other words, I need to
read a text series (row or column) and be able to combine and display them
in one cell by skipping a set number of cells. Thanks for any help.



Current Function

Function ConRange(CellBlock As Range) As String
For Each cell In CellBlock
ConRange = ConRange & cell.Value
Next
End Function
 
To get the offset of a cell within the block (where 0 = first cell, 1 =
second cell, etc.), use the expression:
(cell.row-cellblock.row)*cellblock.columns+cell.column-cellblock.column
Then test this value mod 2 to process every other cell (or mod 3 to process
or skip every third cell, etc.) before doing the concatenatiuon.
 
Thanks for the answer, however I am quite new at this. Could you give me an
example to go by. Thanks.
 
Something like this:

Function ConRange(CellBlock As Range) As String
Dim cellNumber As Integer
For Each cell In CellBlock
cellNumber = (cell.Row - CellBlock.Row) * CellBlock.Columns.Count +
cell.Column - CellBlock.Column
If cellNumber Mod 2 = 0 Then
ConRange = ConRange & cell.Value
End If
Next
End Function

This will get the first cell and every second one after that
Use 'Mod 2 = 1' to get the second cell and every second one after that
Use 'Mod 3 = 0' to get the first cell and every third one after that.
Use 'Mod 3 = 1' to get the second cell and every third one after that
Use 'Mod 3 = 2' to get the third cell and every third one after that
Use 'Mod 4 = 0' to get the first cell and every fourth one after that
etc.
 
Stephen Bye said:
Something like this:

Function ConRange(CellBlock As Range) As String ....
Click to expand...

If you need to do this frequently, you may be better off downloading and
installing Laurent Longre's MOREFUNC.XLL add-in which provides a function
named MCONCAT that concatenates arbitrary ranges and arrays into single
strings. Download from http://longre.free.fr/english/. To use it to
concatenate every other row in a single column range, use

=MCONCAT(T(OFFSET(SomeRange,
ROW(INDIRECT("1:"&INT((ROWS(SomeRange)+1)/2)))-1,0,1,1)))

For every third row,

=MCONCAT(T(OFFSET(SomeRange,
ROW(INDIRECT("1:"&INT((ROWS(SomeRange)+2)/3)))-1,0,1,1)))

If you'd prefer using a udf, then make it flexible. Allow arrays as well as
ranges. Make the row increment an argument rather than making it necessary
to edit the udf to increment a different number of rows, but make it
optional defaulting to all rows in sequence. Due more to my own
peculiarities, use the sign of the row increment argument to determine
whether concatenation is left to right (+) or right to left (-). Finally, do
sensible things when the first argument is neither a range nor an array and
when the second is zero.


Function CatN(a As Variant, Optional n As Long = 1) As String
Dim x As Variant, d As Boolean, i As Long

If n = 0 Then
CatN = ""
Exit Function
Else
d = (n > 0)
n = Abs(n)
End If

If TypeOf a Is Range Then a = a.Value
If Not IsArray(a) Then
CatN = CStr(a)
Exit Function
End If

For Each x In a
If i Mod n = 0 Then CatN = IIf(d, CatN & CStr(x), CStr(x) & CatN)
i = i + 1
Next x
End Function
 
Back
Top