Test

Just testing - ignore this post

TESTING CODE PASTE ERROR

Code:

---

'  http://www.excelfox.com/forum/showthread.php/2465-copy-paste-conditional


Sub CopyPasterConditionalToPut[color=green]Remark_1_2_3_etc() '[/color]
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks("1.xlsx")
 Set Wb2 = ThisWorkbook  '  macro will be placed in 2.xlsm
 Set Ws1 = Wb1.Worksheets.Item(1): Set Ws2 = Wb2.Worksheets.Item(1)
Rem 2 data Input
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
 Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
 Let arr2() = Ws2.Range("A1").CurrentRegion.Value                                    ' Current region will not work for arrS1() because columns G to J are empty
'2b
 ReDim arr3(0 To UBound(arr2(), 1)) ' A 1 dimension array of arrays , ( the first element arr3(0) we will not use )
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output
Rem 3
Dim Cnt
    For Cnt = 2 To UBound(arr2(), 1) '  "row" count, Cnt from after heading untill last row in  2.xlsm ( Ws2 )
    '2b)(ii) make and fill the row element array inside the current arr3(cnt) element
    Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
    Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays. It has one more element than filled columns - this empty last element is filled in the next line
    Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 ' this puts the next integer in the last, currently empty element
    '3a) Check for match criteria
    Dim mtchRes As Variant
    Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
        If IsError(mtchRes) Then  '  If the last line errored than we did not find a match, so from the 3rd up to the last element need to be rtemoved from the array for this row
        Dim Empt As Long
            For Empt = 3 To UBound(arr3(Cnt - 1), 2)
            Let arr3(Cnt - 1)(1, Empt) = ""
            Next Empt
        Else
        ' a match was found, so we do not need to remove the  1  2  3  etc...
        End If
    '3c) Paste out row
    Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
    Next Cnt
                                                                                                                        Rem 4    ....and after putting the remark clear sheet 1 and sheet 2
                                                                                                                        ' Ws1.Cells.Clear
                                                                                                                        ' Ws2.Cells.Clear
End Sub



















'    http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String '        http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

---

Full macro for this post,

Sub CopyPasterConditionalToPutRemark_1_2_3_etcArseOverTit()

Code:

---

'  http://www.excelfox.com/forum/showthread.php/2465-copy-paste-conditional

'  http://www.excelfox.com/forum/showthread.php/2465-copy-paste-conditional?p=13175&viewfull=1#post13175
Sub CopyPasterConditionalToPutRemark_1_2_3_etcArseOverTit() '
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks("1 1Mai.xlsx")
 Set Wb2 = ThisWorkbook  '  macro will be placed in 2.xlsm
 Set Ws1 = Wb1.Worksheets.Item(1): Set Ws2 = Wb2.Worksheets.Item(1)
Rem 2 data Input
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
 Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
 Let arr2() = Ws2.Range("A1").CurrentRegion.Value                                    ' Current region will not work for arrS1() because columns G to J are empty
'2b
 ReDim arr3(0 To UBound(arr2(), 1)) ' A 1 dimension array of arrays , ( the first element arr3(0) we will not use )
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output
Rem 3
Dim Cnt
    For Cnt = 2 To UBound(arr2(), 1) '  "row" count, Cnt from after heading untill last row in  2.xlsm ( Ws2 )
    '2b)(ii) make and fill the row element array inside the current arr3(cnt) element
    Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
    Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays. It has one more element than filled columns - this empty last element is filled in the next line
    Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 ' this puts the next integer in the last, currently empty element
    '3a) Check for match criteria
    Dim mtchRes As Variant
    Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
        If IsError(mtchRes) Then  '  If the last line errored than we did not find a match, so we do not need to  do anything to the array
        ' a match was not found, so we do not need to remove the  1  2  3  etc...
        Else
        ' a match was found, so we need to remove the  1  2  3  etc...
        Dim Empt As Long
            For Empt = 3 To UBound(arr3(Cnt - 1), 2)
            Let arr3(Cnt - 1)(1, Empt) = ""
            Next Empt
        End If
    '3c) Paste out row
    Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
    Next Cnt
                                                                                                                        Rem 4    ....and after putting the remark clear sheet 1 and sheet 2
                                                                                                                        ' Ws1.Cells.Clear
                                                                                                                        ' Ws2.Cells.Clear
End Sub



















'    http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String '        http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function


---

Test



askfasfjf

can I post in firefox
Yes... looks like Google Chrome is quirky ( IE as well, but that always was ) ... https screws it up again I expect

x

Notes to assist me in answering this Thread:
https://excelfox.com/forum/showthrea...-with-Matching

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

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	UserId	AccountId	EntityName	Exchg-Seg	Symbol	Instrument Name	Option Type	NetBuyValue	NetSellValue	NetValue	NetBuyQty	NetSellQty	NetQty	BEP	SellAvgPrice	BuyAvgPrice	LastTradedPrice	MarkToMarket	Realized MarkToMarket	Unrealized MarkToMarket	EL MarkToMarket	Trading Symbol	Client Context	Series/Expiry	Strike Price
2		WC5758		NSE	AMBUJACEM	EQ		10781.10	10878.30	97.20	54	54			201.45	199.65	201	97.2	-97.2		97.2	AMBUJACEM-EQ		EQ
3		WC5758		NSE	ADANIENT	EQ		420.60	430.50	9.90	2	2			215.25	210.30	210.35	9.9	9.9		9.9	ADANIENT-EQ		EQ
4		WC5758		NSE	MARICO	EQ		1688.40	1713.00	24.60	6	6			285.50	281.40	281.9	24.6	24.6		24.6	MARICO-EQ		EQ
5		WC5758		NSE	APOLLOTYRE	EQ		2429.10	2405.70	-23.40	18	18			133.65	134.95	135	-23.4	-23.4		-23.4	APOLLOTYRE-EQ		EQ
6		WC5758		NSE	L&TFH	EQ		1765.80	1794.60	28.80	18	18			99.70	98.10	98.25	28.8	28.8		28.8	L&TFH-EQ		EQ

Worksheet: ap-Sheet1

_____ Workbook: Book1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
1	Symbol
2	AMBUJACEM	22	1	2	3	4	5
3	ADANIENT	25	1
4	APOLLOTYRE	15083
5	ADANIPOWER	17388	1	2	3
6

Worksheet: Sheet1

_____ Workbook: Book1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Symbol
2	AMBUJACEM	22	1	2	3	4	5
3	ADANIENT	25	1
4	APOLLOTYRE	15083	1	2	3	4	5	6	7
5	ADANIPOWER	17388	1	2	3
6

Worksheet: Sheet3


macro will be placed in Book1.xlsm
We have to look on Column S of ap.xls and If column S of ap.xls has negative numbers then we have to look on Column E of ap.xls & we will match that Column E data of ap.xls with column A of sheet1 of Book1.xlsm & if it matches then we will see wheather column C of Book1.xlsm has data in it or not & if column C of Book1.xlsm has data in it then do nothing & if column C of Book1.xlsm doesn't have data then we will go to sheet3 of Book1.xlsm and we will look for a match of Column E data of ap.xls with column A of sheet3 of Book1.xlsm & if it is found then we will copy the data from sheet3 of Book1.xlsm and paste it to sheet1 of book1.xlsm & we increase one more number in series in it

After

_____ Workbook: Book1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Symbol
2	AMBUJACEM	22	1	2	3	4	5
3	ADANIENT	25	1
4	APOLLOTYRE	15083	1	2	3	4	5	6	7	8
5	ADANIPOWER	17388	1	2	3
6
7

Worksheet: Sheet1

macro in next post...

Macro needed , working on the Before of thee last post, which will produce the After of the last post
Macro to solve this Thread : https://excelfox.com/forum/showthrea...-with-Matching
https://excelfox.com/forum/showthrea...ll=1#post13414

Code:

---

'                Conditionally Copy & Paste of the data with increasing series with Matching
Sub Step15() '  https://excelfox.com/forum/showthread.php/2498-Conditionally-Copy-amp-Paste-of-the-data-with-increasing-series-with-Matching
Rem worksheets info
'  ap.xls
Dim Wbap As Workbook
 Set Wbap = Workbooks("ap.xls")
Dim Wsap As Worksheet
 Set Wsap = Wbap.Worksheets.Item(1)
Dim Lrap As Long: Let Lrap = Wsap.Range("E" & Wsap.Rows.Count & "").End(xlUp).Row
Dim Arrap As Variant: Let Arrap = Wsap.Range("A1:Y" & Lrap & "").Value2
'  Book1.xlsm
Dim Wb1 As Workbook
 Set Wb1 = ThisWorkbook
Dim Ws1 As Worksheet, Ws3 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1): Set Ws3 = Wb1.Worksheets.Item(3)
Dim Lr1 As Long: Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2  '        column A of sheet1 of Book1.xlsm
Dim arrC() As Variant: Let arrC() = Ws1.Range("C1:C" & Lr1 & "").Value2  '        column C of sheet1 of Book1.xlsm
Dim Lr3 As Long: Let Lr3 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA3() As Variant: Let arrA3() = Ws3.Range("A1:A" & Lr1 & "").Value2 '        column A of sheet3 of Book1.xlsm
Rem We have to look on Column S of ap.xls and If column S of ap.xls has negative numbers then
Dim Cnt As Long
    For Cnt = 2 To Lrap ' going down rows in ap worksheet 1
        If Arrap(Cnt, 19) < 0 Then '  If column S of ap.xls has negative numbers then
        Dim Eap As String: Let Eap = Arrap(Cnt, 5) ' then we have to look on Column E of ap.xls  -  Column E data
        Dim mtchRes As Variant ' & we will match that Column E data of ap.xls with column A of sheet1 of Book1.xlsm
        Let mtchRes = Application.Match(Eap, arrA(), 0)
            If IsError(mtchRes) Then
            ' no match
            Else ' see whether column C of Book1.xlsm has data in it or not
                If arrC(mtchRes, 1) = "" Then
                Dim mtchRes3 As Variant '  go to sheet3 of Book1.xlsm and we will look for a match of Column E data of ap.xls with column A of sheet3 of Book1.xlsm
                Let mtchRes3 = Application.Match(Eap, arrA3(), 0)
                    If IsError(mtchRes3) Then
                    ' no match
                    Else
                    Dim Lc As Long: Let Lc = Ws3.Cells.Item(mtchRes3, Ws3.Cells.Columns.Count).End(xlToLeft).Column
                    Dim arr3() As Variant
                    Let arr3() = Ws3.Range("A" & mtchRes & ":" & CL(Lc + 1) & mtchRes & "").Value ' An array for all data of that row in sheet3 and an extra column
                    Let arr3(1, UBound(arr3, 2)) = UBound(arr3(), 2) - 2 ' this puts the next integer in the last, currently empty element    ............ increase one more number in series in it
                    '  we will copy the data from sheet3 of Book1.xlsm and paste it to sheet1 of book1.xlsm  & we ...................................      increase one more number in series in it
                    '  Paste out row
                    Let Ws1.Range("A" & mtchRes & "").Resize(1, Lc + 1).Value = arr3()
                    End If
                Else '  column c has data in it
                ' do nothing
                End If
               
            End If
           
        Else ' not a negative in S column
       
        End If
   
   
   
    Next Cnt
End Sub
'
'    http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String '        http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

---

---

Macro is also in Book1.xlsm
Share ‘Book1.xlsm’ : https://app.box.com/s/qotw65wmiq1aln7frg9o5gys8ke1l8xh
Share ‘ap.xls’ : https://app.box.com/s/r0tc0hkwoxrqjsqfu4gh7eqyy5jmqlg2

Just testing colours in code....

standard:

Code:

---

'  The solution from karmapala at excelforum 20 June 2020
'  https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174
Sub karmapala()
'Dim arr() As Variant
Dim Wb1 As Workbook, Wb2 As Workbook, Sh1 As Worksheet, Sh2 As Worksheet
Set Wb1 = Workbooks("1.xls")
Set Sh1 = Wb1.Worksheets.Item(1)  ' Wb1.Sheets("1-Sheet1")
Dim Rng As Range ' For main data range in 1.xls
' Set Rng = Sh1.Range("D2", Sh1.Range("D" & Rows.Count).End(xlUp)) ' This and the next line will error if macro.xlsm is active when the macro is run as Rows.Count will give a much larger number ( 1048576 ) than there are rows in a pre Excel 2007 worksheet ( .
' Set Rng = Sh1.Range(Sh1.Range("D2"), Sh1.Range("D" & Rows.Count).End(xlUp))'
Set Rng = Sh1.Range("D2", Sh1.Range("D" & Sh1.Rows.Count).End(xlUp))
Set Wb2 = Workbooks("macro.xlsm") ' Workbooks("Macro.xlsm")
Set Sh2 = Wb2.Worksheets.Item(1)  ' Wb2.Sheets("Sheet1")
Dim X As Long
X = 0
Rem 2 In this section we build an array, arr(),  of column I values to be                                              ...  match Column I of 1.xls with column B of macro.xlsm
Dim Cel As Range
    For Each Cel In Rng
    Dim arr() As Variant ' This will become the array of column I values to be                                          ...  match Column I of 1.xls with column B of macro.xlsm
        If Cel.Value = Cel.Offset(0, 1).Value And Cel.Value <> Cel.Offset(0, 2).Value Then
        ' If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls Then ...
        ReDim Preserve arr(X)
        arr(X) = Cel.Offset(0, 5) ' This is the column I value for                                                      ... match Column I of 1.xls with column B of macro.xlsm
        X = X + 1 ' to make the array element for the next entry, should there be one
        End If

        'If Cel.Value <> Cel.Offset(0, 1) And Cel.Value = Cel.Offset(0, 2) Then
        If Cel.Value = Cel.Offset(0, 2) And Cel.Value <> Cel.Offset(0, 1) Then  '                                      ....
        ReDim Preserve arr(X)
        ReDim Preserve arr(X)
        arr(X) = Cel.Offset(0, 5) ' This is the column I value for                                                      ... match Column I of 1.xls with column B of macro.xlsm
        End If
    Next

    If X = 0 Then Exit Sub

Rem 3 In this section we
Dim El
    For Each El In arr() ' arr
    Dim C
    Set C = Sh2.Range("B:B").Find(El, lookat:=xlWhole)
        If Not C Is Nothing Then
        FirstAddress = C.Address
            Do
                If C.Offset(0, 1).Value = "" Then
                C.Offset(0, 1).Value = 1
                Else
                C.End(xlToRight).Offset(0, 1).Value = C.End(xlToRight).Value + 1
                End If
            Set C = Sh2.Range("B:B").FindNext(C)
            Loop While C.Address <> FirstAddress
        End If
    Next

End Sub

---

testing following on from last post...

What can young Kyle offer me.....

Code:

---

'  The solution from karmapala at excelforum 20 June 2020
'  https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174
Sub karmapala()
'Dim arr() As Variant
Dim Wb1 As Workbook, Wb2 As Workbook, Sh1 As Worksheet, Sh2 As Worksheet
Set Wb1 = Workbooks("1.xls")
Set Sh1 = Wb1.Worksheets.Item(1)  ' Wb1.Sheets("1-Sheet1")
Dim Rng As Range ' For main data range in 1.xls
' Set Rng = Sh1.Range("D2", Sh1.Range("D" & Rows.Count).End(xlUp)) ' This and the next line will error if macro.xlsm is active when the macro is run as Rows.Count will give a much larger number ( 1048576 ) than there are rows in a pre Excel 2007 worksheet ( .
' Set Rng = Sh1.Range(Sh1.Range("D2"), Sh1.Range("D" & Rows.Count).End(xlUp))'
Set Rng = Sh1.Range("D2", Sh1.Range("D" & Sh1.Rows.Count).End(xlUp))
Set Wb2 = Workbooks("macro.xlsm") ' Workbooks("Macro.xlsm")
Set Sh2 = Wb2.Worksheets.Item(1)  ' Wb2.Sheets("Sheet1")
Dim X As Long
X = 0
Rem 2 In this section we build an array, arr(),  of column I values to be                                              ...  match Column I of 1.xls with column B of macro.xlsm
Dim Cel As Range
    For Each Cel In Rng
    Dim arr() As Variant ' This will become the array of column I values to be                                          ...  match Column I of 1.xls with column B of macro.xlsm
        If Cel.Value = Cel.Offset(0, 1).Value And Cel.Value <> Cel.Offset(0, 2).Value Then
        ' If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls Then ...
        ReDim Preserve arr(X)
        arr(X) = Cel.Offset(0, 5) ' This is the column I value for                                                      ... match Column I of 1.xls with column B of macro.xlsm
        X = X + 1 ' to make the array element for the next entry, should there be one
        End If

        'If Cel.Value <> Cel.Offset(0, 1) And Cel.Value = Cel.Offset(0, 2) Then
        If Cel.Value = Cel.Offset(0, 2) And Cel.Value <> Cel.Offset(0, 1) Then  '                                      ....
        ReDim Preserve arr(X)
        ReDim Preserve arr(X)
        arr(X) = Cel.Offset(0, 5) ' This is the column I value for                                                      ... match Column I of 1.xls with column B of macro.xlsm
        End If
    Next

    If X = 0 Then Exit Sub

Rem 3 In this section we
Dim El
    For Each El In arr() ' arr
    Dim C
    Set C = Sh2.Range("B:B").Find(El, lookat:=xlWhole)
        If Not C Is Nothing Then
        FirstAddress = C.Address
            Do
                If C.Offset(0, 1).Value = "" Then
                C.Offset(0, 1).Value = 1
                Else
                C.End(xlToRight).Offset(0, 1).Value = C.End(xlToRight).Value + 1
                End If
            Set C = Sh2.Range("B:B").FindNext(C)
            Loop While C.Address <> FirstAddress
        End If
    Next

End Sub

---

Test Images

Upload icon wont worK ???? error*******

try Imgur.com

1_ link
https://imgur.com/UfmFTQy

2_ HTML embed

HTML Code:

---

<blockquote class="imgur-embed-pub" lang="en" data-id="UfmFTQy"><a href="https://imgur.com/UfmFTQy">View post on imgur.com</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

---

<blockquote class="imgur-embed-pub" lang="en" data-id="UfmFTQy"><a href="https://imgur.com/UfmFTQy">View post on imgur.com</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

3_ BBCode(Forums)
[img]https://i.imgur.com/UfmFTQy.jpg[/img]
https://i.imgur.com/UfmFTQy.jpg

4_ Markdown (Reddit)
[Imgur](https://imgur.com/UfmFTQy)

---

BTW **** this was the error on upload attempt:

[IOErrorEvent type="ioError" bubbles=false cancellable=false eventPhase=2 text="Error
#2038"]

Here is a screenshot, to get that error message you hover with the mouse over the red bit:
https://i.imgur.com/ISFb2g0.jpg

test

[img]https://i.imgur.com/UfmFTQy.jpg[/img]
https://i.imgur.com/UfmFTQy.jpg


test

Code:

---

'                                        Index(OneDimensionalArray(),  1                          ,      1
'                                                                      1                                  2
'                                                                      1                                  3
'                                                                      1                                  4
'                                                                      1                                  5              )

---

Code:

---

'                                        Index(OneDimensionalArray(),  1                          ,      1
'                                                                      1                                  2
'                                                                      1                                  3
'                                                                      1                                  4
'                                                                      1                                  5              )

---

Code:

---

'  Head1  ,  Haed2  ,  Head3
'    2    ,    7    ,  4
'    3    ,    9    ,  5

---

Code:

---

'                                Index(  Head1 , 2, 3          1  ,    1  ,  1                  1  ,  2  ,  3
'                                        Head3 , 4, 5          3  ,    3  ,  3                  1  ,  2  ,  3
'                                        Haed2 , 7, 9          2  ,    2  ,  2                  1  ,  2  ,  3          )

---

In support of this post:
https://www.excelforum.com/excel-pro...ml#post5410028

I am not totally sure what the OP is asking.
Is the OP asking
(i) _ to put values into an existing array where that existing array already has values in it
or
(ii)_ changing the array dimension and positioning of elements in an array
or
(iii)_ maybe its lost in the translation and/ or the OP is not sure him/herself.
The initial answer to (i)_ I think we seem clear about:- It will likely in VBA require a code line for each element to be "moved" from one array to the other , so likely looping will be involved for a multi element array.
The Thread title and OPs first question infers to me converting a 1 D array to a 2 D array, without looping.
If the existing array with values already in it is a dynamic array, then overwriting along with re dimensioning means that those (i)_ and (ii)_ are somewhat merged in meaning anyway.
So I am not totally clear what is going on here, but I think it there is a discussion of generally … …"1 D arrays to 2 D arrays"
So lets say we are talking generally about …"1 D arrays to 2 D arrays" and leave it loosely defined for now and go with that…

Frederick has shown in his second code line that a characteristic of the Transpose function is that if a 1 D array is given to the Transpose function then the transposed array becomes a 2 D array , all be it a quasi "1 column array" ***
Transpose does that, as it does the opposite way converting a single column 2D array to a 1D array.
I think most of us are not quite sure why it has been wired to do that. Some other things seem to default to making a "one row" thing be a 1D array rather than a 2D array, even when the thing it may have been given to work on was a 2D array. ( It does not screw things up to badly when playing with spreadsheets since that transposed in its final 1 D form will be "seen" by Excel as if it was a single row 2 Dimensional array when applied to a spreadsheet range. So usually a "row" becomes a row, if you catch my drift).
We can go the other way. ( If we do that with Rick's example , we will see a small difference, the 1 D array returned will have indices of 1 2 3 4 5 as opposed to the 0 1 2 3 4 , (since the Split function Rick used returns those starting a base 0 ) . I am not sure why Excel chooses to start a t 1 in this case: Possibly it was just made that way because its more often to do with worksheet/spreadsheet stuff, and we think about rows and columns starting at 1, and something like a row of 1 is a bit stupid. )

Index with arrays as co ordinate arguments
This stuff is worth knowing about:
A further function that can be very helpful in doing this sort of manipulation of arrays without looping is the Index Function. It becomes so useful because it will accept arrays in place of the more conventional single value indices in its second ( row ) and third ( column ) arguments. The evaluation is then done in the conventional Excel way, "along the columns of a row" , then down to repeat at the next row: along the columns of that row, then down to repeat at the next row: along the columns of that row, then down to repeat at the next row: along the columns of that row , ….etc. Usually VBA will do its best to give out the results in an array dimensioned appropriate for the array dimensions supplied in those second and third arguments, following the conventional "along the columns of a row" , then down to repeat at the next row: along the columns of that row, ………

As example we can do that Transpose code line in this pseudo way

Code:

---

'                                        Index(OneDimensionalArray(),  1                          ,      1
'                                                                      1                                  2
'                                                                      1                                  3
'                                                                      1                                  4
'                                                                      1                                  5              )

---

We are doing 5 calculations there, talking each time the first row and consecutive columns, the result coming out in a form that the Excel calculations are done - .. "along the columns of a row" , then down to repeat at the next row… but we only have one column in this case, so that is actually just going down the rows, 5 times. Hence our output is the 90degree transpose of OneDimensionalArray()

That was just one example, but the important point is that you can supply different arrays in the Index second ( "row" ) and third ( "column" ) arguments. So you can pretty well take any1 or 2 D array in the Index first argument, and in one code line, without looping , put all or some of the values from that array in some other order in any other 1 or 2 D array. That could be what the OP was asking for ….
Dim Array1(2, 2) As Integer
Dim Array2(2) As Integer
…………… way to copy the values from Array2 into Array1?
The restriction is that we can't make use of this to put values into Array1( ) if it already existed. You would have to be in like having
Dim Array1() As Variant
Dim Array2(2) As Integer
-……..
Array1()= Index ( Array2(2) , { _.... } , { _... } )
( Variant is needed in the first declaration as the index chucks its output values housed in Variant types. AFAIK the first argument can be any sort of 1 D or 2 D array, ( or it can be any range object ) )

Another not looping option to assist in a conversion could be to remove rows or columns of a 2 D array with a single code line. Best look at some posts of Rick ( Frederick Rothstein 's ) , stuff for that ( https://excelfox.com/forum/showthrea...-Variant-Array )


One last curiosity , a weird thing I only recently came across. An array of arrays, sometimes refereed as a "jagged array", is peculiarly treated in some cases by Index as a 2 D array. This gives us some interesting further one liner code line possibilities.
Example, If I had a 1 D array of 1 D arrays, something of this sort of form
{ { "Head1" , 2, 3 } , {"Head3", 4, 5 } , {"Haed2", 7, 9} }
then I can convert that, for example, to re ordered in data columns like this

Code:

---

'  Head1  ,  Haed2  ,  Head3
'    2    ,    7    ,  4
'    3    ,    9    ,  5

---

I can do that using like a Index one code liner pseudo

Code:

---

'                                Index(  Head1 , 2, 3          1  ,    3  ,  2                  1  ,  1  ,  1
'                                        Head3 , 4, 5          1  ,    3  ,  2                  2  ,  2  ,  2
'                                        Haed2 , 7, 9          1  ,    3  ,  2                  3  ,  3  ,  3          )

---

I put some more details of all I have been saying , in a macro in the uploaded file. Probably its best to step through the macro in Debug mode ( do that by hitting Key F8 after clicking anywhere in the macro )

---

Hello Adam.
I expect you are referring specifically to the idea of putting existing values from an array into another existing array, although I am not fully clear if the OP wanted that: Possibly the language barrier prevented the OP getting anything out of the links you gave him…. The best thing probably, as Rory asked for, was an example from the OP of what he wanted to do…
Anyway, you probably know all the following, but I thought I'd add it to the Thread, while I am in the mood…
Generally questions along the lines of "1 D array to 2 D array" or visa versa are quite common in Excel VBA. I expect this is because
_ a) a lot of things done "internally" in coding involve 1 D arrays,
but/ and
_ b) a range from a spreadsheet will often likely end up in an array of 2 Dimensions, I think Excel does this so that we can make the distinction what is a row and what is a column.***
So things might not always work as we wanted, for example a problem might occur when a 1 D array appears when a 2 D array was expected/ wanted, and visa versa. To solve the problem a conversion from a 1D to 2D or visa versa might get us out of trouble.
Example: we got a Join function that is something like the reverse of the Split function mentioned in this Thread . Basically you can use it to join the contents of an array into a string. The bummer is that it only accepts a 1 D array. So if I give it a column or row of data to Join it will error. You'll need to change the 2D array got from a spreadsheet single row or a spreadsheet single column to a 1D array for join to work on it. ( One way you can do that is with some of the one liner codings I been talking about