Solved: Creating an Array via a Range

[anthony20069]

Hi all,

So i have a hardcoded array as below:

[VBA]Array("1", "2", "3", "4", "5", "6")[/VBA]

Now is it possible it define a range and replace 1 - 6 with what ever values are in this range. The range could be longer or shorter than 6 values.

Many thanks

[snb]

[vba]
sn=range("A1:F1")
[/vba]
or
[vba]sn=range("A1:A6")[/vba]
or [VBA]sn=Range("A1:F6")[/VBA]

[anthony20069]

Ok thanks for that,

so would the VBA be:

[VBA]Dim sn as range

set sn = .Range("B1:B" & end of row) 'have VBA to find the end of row

Array(sn) [/VBA]

[snb]

this suffices:

[vba]sub M_snb()
sn=columns(2).specialcells(2)
end sub[/vba]

PS the resulting variable 'sn' is an array

[Kenneth Hobs]

[VBA]Sub Ken()
Dim a() As Variant
a() = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("B1", Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp)))
MsgBox Join(a(), vbLf)
End Sub[/VBA]

[anthony20069]

Hi guys,

thanks for your replies:

@Ken - i tried yours, but it errors out
@snb - sorry, not really sure how to use your one - still new to VBA

the actual range starts at J13:J(End of row) if that helps?

[Kenneth Hobs]

You will find if you review my posts that I most always test my code. Howsoever, I have to make some basic assumptions and leave it to the user to code for scenarios that cause errors.

Obviously, it would error if you change your criterion. The error may be telling you that Column "B" has no data. So, Change "B" to "J" and on sheet1 put some data in column "J" before trying.

Code like this makes some assumptions. e.g.
1. Data is in column "J".
2. The macro is ran from the workbook with this code.
3. Sheet1 exists.

[vba]Sub Ken()
Dim a() As Variant
a() = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("J13", Worksheets("Sheet1").Range("J" & Rows.Count).End(xlUp)))
MsgBox Join(a(), vbLf)
End Sub [/vba]
If you post example workbooks for us to help you with, many assumptions can be made correctly. Half the work in solving a problem is defining it properly. Solutions will very much depend on things like data starting in J13 versus B1 for example.

[snb]

I second KH

[anthony20069]

You will find if you review my posts that I most always test my code. Howsoever, I have to make some basic assumptions and leave it to the user to code for scenarios that cause errors.

Obviously, it would error if you change your criterion. The error may be telling you that Column "B" has no data. So, Change "B" to "J" and on sheet1 put some data in column "J" before trying.

Code like this makes some assumptions. e.g.
1. Data is in column "J".
2. The macro is ran from the workbook with this code.
3. Sheet1 exists.

[vba]Sub Ken()
Dim a() As Variant
a() = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("J13", Worksheets("Sheet1").Range("J" & Rows.Count).End(xlUp)))
MsgBox Join(a(), vbLf)
End Sub [/vba]
If you post example workbooks for us to help you with, many assumptions can be made correctly. Half the work in solving a problem is defining it properly. Solutions will very much depend on things like data starting in J13 versus B1 for example.

Im not doubting your code doesn't work, more along the lines of i have not used it properly.

The range needs to be defined within another workbooks called "Main.xlsx" and in the worksheet called "Data". I tried:
[VBA]aHeaders() = WorksheetFunction.Transpose(Workbooks("Main.xlsm").Worksheets("Datat").Rang e("J13", Workbooks("Main.xlsm").Worksheets("Data").Range("J" & Rows.Count).End(xlUp)))[/VBA]
But it gives me a "Run-time error '9': Script out of range"?

[anthony20069]

Thanks for you help, got it working

[Kenneth Hobs]

Good deal. I guess you noticed the typo in the name of your sheet, Datat vs. Data.

My main point was that errors will occur during run-time if your code does not address them. We can post solutions to get you started but a full solution should always Try to Catch errors. We generally leave that up to you though some of us put some error checks on occasion. It can be tedious trying to figure out all the things that might go wrong during run-time but worked fine in your test runs.

cheers