PDA

View Full Version : [SOLVED] Write array to row (not column)



gmaxey
09-30-2019, 02:52 PM
Guys,

Sometimes I can eek out an Excel problem but others I am just a noob.

I have a simple 1 dimensional array. I need to populate the values "across" the row (not down the rows in a single column)



Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim arr(3) As String
arr(0) = "A"
arr(1) = "B"
arr(2) = "C"
arr(3) = "D"
Sheet1.Range("A1").Resize(UBound(arr) - LBound(arr) + 1).Value = WorksheetFunction.Transpose(arr)
lbl_Exit:
Exit Sub
End Sub

With transpose, the data is right but down column A in four rows. Without transpose it still goes down column A in four rows with "A" in each row

How can I get "A" in A1, "B" in B1, "C" in C1 and so on.

Note this is just an example. My array needs to be one dimensional.

Thanks

Kenneth Hobs
09-30-2019, 03:24 PM
You just needed to resize columns, not rows, so I added a comma and removed transpose.
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey
Dim arr(3) As String, a
arr(0) = "A"
arr(1) = "B"
arr(2) = "C"
arr(3) = "D"
Sheet1.Range("A1").Resize(, UBound(arr) + 1).Value = arr

a = Split("A,B,C,D", ",")
Sheet1.[A2].Resize(, UBound(a) + 1) = a
End Sub

gmaxey
09-30-2019, 05:05 PM
thanks Kenneth. I'll give that a try.

Paul_Hossler
09-30-2019, 05:16 PM
Another way -- a double Transpose to put a 1D array across rows




Sheet1.Range("A1").Resize(1, UBound(arr) - LBound(arr) + 1).Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arr))

gmaxey
09-30-2019, 05:46 PM
Thanks Paul,

You know somewhere in the fog I see visions of being here before. LOL.

You guys are a great help.

snb
10-01-2019, 12:17 AM
@PH

I don't see any advantage (more the contrary) to


Sub M_snb()
sn = Split("A B C D")
Cells(1).Resize(,UBound(sn) + 1) = sn
End Sub

To illustrate an alternative:


Sub M_snb()
sn = Split("A B C D")
Cells(1).Resize(, UBound(sn) + 1) = Application.Index(sn, 1, 0)
End Sub
or

Sub M_snb()
sn = Split("A B C D")
Cells(1).Resize(, UBound(sn) + 1) = Application.Index(sn, 0, 0)
End Sub

Paul_Hossler
10-01-2019, 06:14 AM
@PH

I don't see any advantage (more the contrary) to ...


Maybe, but it's what I'm used to. Your second and third 'feel' more abstract, but I will have to play with your first one though

snb
10-01-2019, 07:47 AM
More abstract I'd call:


Sub M_snb()
With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
.List = Split("A B C D E F G")
Sheet1.Cells(1).Resize(, .ListCount) = .Column
End With
End Sub