Output Array of Strings onto new range

Question

I'm attempting to grab strings from a specific range of one worksheet and output the array onto another sheets range. Unfortunately the resulting code gives me no output of any sort.

Thank you for the help in advance.

Dim strFunds As String
Dim varItemName As Variant
Dim strItemName() As String

Dim iRow As Long
Dim rngMyRange As Range


Sub DataGrab()

ActiveWorkbook.Sheets("Returns").Activate


Set rngMyRange = Range("A5:A100")
varItemName = rngMyRange
ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1))

ActiveWorkbook.Sheets("Data").Activate

Range("A3:A" & UBound(strItemName) + 1) = WorksheetFunction.Transpose(strItemName)

End Sub

score 2 · Accepted Answer · answered May 28 '14 at 15:33

2

Sub Main()

    Dim rngArr
    rngArr = Sheets("Returns").Range("A5:B100").Value

    Sheets("Data").Range("A3").Resize(UBound(rngArr) + 1, 2) = rngArr
End Sub

answered May 28 '14 at 15:33

score 1 · Answer 2 · answered May 28 '14 at 17:34

EDIT: Oops didn't see me how's answer above. This answer is pretty much the same thing.

Try this.

First, change varItemName to an array:

Dim varItemName() As Variant

Then:

Sub DataGrab()

ActiveWorkbook.Sheets("Returns").Activate    

Set rngMyRange = Range("A5:A100")
varItemName = rngMyRange.Value
'ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1))

ActiveWorkbook.Sheets("Data").Activate

Range("A3").Resize(1, UBound(varItemName) + 1) = WorksheetFunction.Transpose(varItemName)

End Sub

That is assuming you want to convert your columnar data into a single row. If not, do this on the last line instead:

Range("A3").Resize(UBound(varItemName) + 1, 1) = varItemName

S. Ahn · Answer 3 · 2014-05-28T15:23:51.210

0

You are not assigning any values to strItemName. Here's a version that just keeps everything in varItemName.

ActiveWorkbook.Sheets("Returns").Activate

Set rngMyRange = Range("A5:A100")
varItemName = rngMyRange.Value

ActiveWorkbook.Sheets("Data").Activate

Range(Cells(3, 1), Cells(3, UBound(varItemName))) = WorksheetFunction.Transpose(varItemName)

UPDATE: If you don't need to save varItemName, you can use this shorter version:

Set rngMyRange = Range("A5:A100")
ActiveWorkbook.Sheets("Data").Activate
Range(Cells(3, 1), Cells(3, 100)) = WorksheetFunction.Transpose(rngMyRange)

edited May 28 '14 at 15:23

answered May 28 '14 at 14:45

S. Ahn

633
5
8

I get a runtime error of '13' Type mismatch. I have switched the range A5:A100 to B5:B100 but I'm not sure why this is causing the error. – user3684060 May 28 '14 at 15:01

Output Array of Strings onto new range

3 Answers3