匪夷所思的Range()合并区域赋值问题

aman1516

简单中隐藏着这么多的不简单，学习了

2007小金猪

收藏学习。

wajour

个人觉得这个类似双for each的写入
而其for each 的循环顺序是反N字形，就是从左到右，从上到下的顺序写入的。
总结一下其实这个跟transpose写入是有区别的，当然当数据只有一行时是一样的

wajour

模拟了一个双foreach

wajour

1. Sub 测试4X2()
   
2.     Dim rng1 As Range, rng2 As Range, intCounter As Integer, intCounter2 As Integer, Counter As Integer
   
3.     Dim rngArr1() As Range, rngArr2() As Range
   
4.     Set rng1 = Range("A1:B4"): Set rng2 = Range("D3:G4")
   
5.     Range("A1:B4,D1:G2").Value = rng1.Value
   
6.     ReDim rngArr1(1 To rng1.Count): ReDim rngArr2(1 To rng2.Count)
   
7.     For intCounter = 1 To rng1.Columns.Count
   
8.         For intCounter2 = 1 To rng1.Rows.Count
   
9.             Count = Count + 1
   
10.             Set rngArr1(Count) = rng1(intCounter2, intCounter)
    
11.         Next
    
12.     Next
    
13.     Count = 0
    
14.     For intCounter = 1 To rng2.Columns.Count
    
15.         For intCounter2 = 1 To rng2.Rows.Count
    
16.             Count = Count + 1
    
17.             Set rngArr2(Count) = rng2(intCounter2, intCounter)
    
18.         Next
    
19.     Next
    
20.     For Count = 1 To rng1.Count
    
21.         Debug.Print rngArr1(Count).Address; "→"; rngArr2(Count).Address
    
22.         rngArr2(Count).Value = rngArr1(Count).Value
    
23.     Next
    
24. End Sub
    
25. Sub 测试4X1()
    
26.     Dim rng1 As Range, rng2 As Range, intCounter As Integer, intCounter2 As Integer, Counter As Integer
    
27.     Dim rngArr1() As Range, rngArr2() As Range
    
28.     Set rng1 = Range("A7:A10"): Set rng2 = Range("D7:G7")
    
29.     Range("A7:A10,D8:G8").Value = rng1.Value
    
30.     ReDim rngArr1(1 To rng1.Count): ReDim rngArr2(1 To rng2.Count)
    
31.     For intCounter = 1 To rng1.Columns.Count
    
32.         For intCounter2 = 1 To rng1.Rows.Count
    
33.             Count = Count + 1
    
34.             Set rngArr1(Count) = rng1(intCounter2, intCounter)
    
35.         Next
    
36.     Next
    
37.     Count = 0
    
38.     For intCounter = 1 To rng2.Columns.Count
    
39.         For intCounter2 = 1 To rng2.Rows.Count
    
40.             Count = Count + 1
    
41.             Set rngArr2(Count) = rng2(intCounter2, intCounter)
    
42.         Next
    
43.     Next
    
44.     For Count = 1 To rng1.Count
    
45.         Debug.Print rngArr1(Count).Address; "→"; rngArr2(Count).Address
    
46.         rngArr2(Count).Value = rngArr1(Count).Value
    
47.     Next
    
48. End Sub

复制代码

wajour

1. Function getRange(rng As Range, intIndex As Integer) As Range
   
2.     Dim intCounter As Integer, intCounter2 As Integer, Count As Integer
   
3.     For intCounter = 1 To rng.Columns.Count
   
4.         For intCounter2 = 1 To rng.Rows.Count
   
5.             Count = Count + 1
   
6.             If intIndex = Count Then Set getRange = rng(intCounter2, intCounter): Exit Function
   
7.         Next
   
8.     Next
   
9. End Function
   
10. Sub 测试4X2()
    
11.     Dim rng1 As Range, rng2 As Range, intCounter As Integer
    
12.     Set rng1 = Range("A1:B4"): Set rng2 = Range("D3:G4")
    
13.     For intCounter = 1 To rng1.Count
    
14.         getRange(rng2, intCounter).Value = getRange(rng1, intCounter).Value
    
15.     Next
    
16. End Sub

复制代码

简化版