PDA

View Full Version : Solved: Creating an Array via a Range



anthony20069
01-31-2013, 04:20 AM
Hi all,

So i have a hardcoded array as below:

Array("1", "2", "3", "4", "5", "6")

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
01-31-2013, 04:36 AM
sn=range("A1:F1")

or
sn=range("A1:A6")
or sn=Range("A1:F6")

anthony20069
01-31-2013, 04:46 AM
Ok thanks for that,

so would the VBA be:

Dim sn as range

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

Array(sn)

snb
01-31-2013, 05:53 AM
this suffices:

sub M_snb()
sn=columns(2).specialcells(2)
end sub

PS the resulting variable 'sn' is an array

Kenneth Hobs
01-31-2013, 07:14 AM
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

anthony20069
01-31-2013, 09:42 AM
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
01-31-2013, 09:57 AM
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.

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
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
01-31-2013, 10:24 AM
I second KH

anthony20069
02-01-2013, 02:42 AM
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.

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
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:
aHeaders() = WorksheetFunction.Transpose(Workbooks("Main.xlsm").Worksheets("Datat").Range("J13", Workbooks("Main.xlsm").Worksheets("Data").Range("J" & Rows.Count).End(xlUp)))
But it gives me a "Run-time error '9': Script out of range"?

anthony20069
02-01-2013, 04:55 AM
Thanks for you help, got it working :)

Kenneth Hobs
02-01-2013, 07:40 AM
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