Testing Concatenating with styles

[DocAElstein]

Excel usually has the cell item numbering convention of going along each column in a row, then down to the next row , then along the columns in that row…. etc…
Like, for example, in a 5 column range:

1 2 3 4 5
6 7 8 …… etc

Example Example
Excel Item number for 2 row, 3 column range .Rows.Count=2 , .Columns.Count=3

_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D
12	.Rows.Cnt=2\.Columns.Count=3	Column 1	Column 2	Column 3
13	Row 1	Item number 1	Item number 2	Item number 3
14	Row 2	Item number 4	Item number 5	Item number 6
15
16	Item Number	Row from Item Number	Column from Item Number
17	1	1	1
18	2	1	1
19	3	1	2
20	4	2	2
21	5	2	3
22	6	2	3

Worksheet: Sheet1

_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
16	Item Number	Row from Item Number	Column from Item Number
17	1	=INT((A17-1)/3)+1	=INT((A17-1)/2)+1

Worksheet: Sheet1


Row from Item Number = Integer( (ItemNumber-1)/NumberOfColumns ) + 1
' take the integer of one less than the item number divided by the number of columns, and then finally add 1

Column from Item Number = Integer( (ItemNumber-1)/NumberOfRows ) + 1
' take the integer of one less than the item number divided by the number of rows, and then finally add 1

Row from Item Number = Integer(  (ItemNumber-1)/NumberOfColumns   ) + 1   '  take the integer of   one less than the item number divided by the number of columns, and then finally add 1  

Column from Item Number =  Integer(  (ItemNumber-1)/NumberOfRows  ) + 1     ' take the integer of  one less than the item number divided by the number of rows, and then finally add 1	
  

---

So we can replace the typical looping process of 2 loops for of all columns in all rows , ( or have as an alternative should we not know the row or column but do know the item number ) ._.... Example

' '  https://www.mrexcel.com/board/threads/the-hardest-question-in-excel-history-a-very-smart-vba-macro-to-combine-cell-with-their-styles.808781/#post-3954687    https://excelfox.com/forum/showthread.php/2679-Concatenate-with-style?p=15170&viewfull=1#post15170
Sub ConcatWithStyles3b()
Dim RngSel As Range: Set RngSel = Selection: Set RngSel = Range("A1:F1")
Rem 0a save any formulas, and replace with values
Dim arrFormulas() As Variant
 Let arrFormulas() = RngSel.Formula ' Assuming wew select more than one cell, we will always be presented by  .Value  a 2 dimensional array, ( even if it is a single row or single column )  This codel line will  error if we are using a selection of one cell, since in that case  .Value  only returns a single value which VBA syntax does not allow to  be assigned to a dynmic array
Dim RwCnt As Long, ClmCnt As Long
'    For RwCnt = 1 To RngSel.Rows.Count ' At each row we...._
'        For ClmCnt = 1 To RngSel.Columns.Count '         _.... go along all columns
'            If Left(arrFormulas(RwCnt, ClmCnt), 1) = "=" Then ' case a formula in cell
'             Let RngSel.Item(RwCnt, ClmCnt).Value = RngSel.Item(RwCnt, ClmCnt).Value ' replace the formula with its value
'            Else
'            End If
'        Next ClmCnt
'    Next RwCnt
Dim RwsCnt As Long, ClmsCnt As Long, Itm As Long, ItmCnt As Long
 Let ItmCnt = RngSel.Cells.Count
 Let RwsCnt = RngSel.Rows.Count: Let ClmsCnt = RngSel.Columns.Count
    For Itm = 1 To ItmCnt
        If Left(arrFormulas(Int((Itm - 1) / ClmsCnt) + 1, Int((Itm - 1) / RwsCnt) + 1), 1) = "=" Then ' case a formula in cell
         Let RngSel.Item(Itm).Value = RngSel.Item(Itm).Value ' replace the formula with its value
        Else
        End If
    Next Itm

When is this useful
In the snippet above there are no advantages, but , in a macro, such as that above, you may want later to Loop for each cell in the range, and within the loop refer to an array of values taken in one go from the range.
The array Excel typically returns will have two dimensions corresponding to the Excel worksheet rows and columns.
If you are looping for each cell, Excel will follow the convention of going along each column in a row, then down to the next row , then along the columns in that row…. etc … We can add a count for the Item in the loop at the beginning like

  For Each ACel In RngSel
   Let Itm = Itm + 1
 

A code line like the following then allows us to get at the specific array value

     If Left(arrFormulas(Int((Itm - 1) / ClmsCnt) + 1, Int((Itm - 1) / RwsCnt) + 1), 1) = "=" Then 

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA