I have an Array (dict1) that i want to paste from cells T1-Z1 using the following code
With Sheets("Raw_Data")
.Range(.Cells(1, 20), .Cells(1, 26)).Resize(dict1.Count).Value = Application.Transpose(dict1.keys)
End With
When i use this however, it pastes the values in the Array from T1-T7, and then copies this from columns T-Z. Is there anyway I can change this so it transposes the array to go horizontally
Instead of
.Range(.Cells(1, 20), .Cells(1, 26)).Resize(dict1.Count).Value = Application.Transpose(dict1.keys)
use
.Range("T1").Resize(1, UBound(Application.Transpose(dict1.keys))) = dict1.keys
Try following code
Sub Demo()
Dim dict1 As Object
Set dict1 = CreateObject("Scripting.Dictionary")
'assume following are the disctionary items
dict1.Add "Aaa", 1
dict1.Add "Bbb", 2
dict1.Add "Ccc", 3
dict1.Add "Ddd", 4
dict1.Add "Eee", 5
dict1.Add "Fff", 6
dict1.Add "Ggg", 7
'output dictionary horizontally
With Sheets("Raw_Data")
.Range("T1").Resize(1, UBound(Application.Transpose(dict1.Keys))) = dict1.Keys
End With
End Sub
This reults in following image.
The original array was loaded from a vertical range
avarPercentValues() = .Range(cstrColTotals & clngAverageSpread & ":" & cstrColTotals & clngValidGain).Value2
The easiest and fastest way to save it horizontally:
.Range(cstrColTAvgSpread & lngDataRow & ":" & cstrColTValidGain & lngDataRow).Value2 = Application.Transpose(avarPercentValues())
cxxxYY are constants for columns On my machine I lose 4 µs om transpose.
