Read a range into an array

L

L. Howard

I found this code that makes a DV drop down.
The example to populate the array was like this, which produced a DV with selections of 1, 2, 3, 4, 5.

Dim ValidationList(5) As Variant

Using the example from a Chip P's site (commented out in the code) I want the list in column F to be the DV source list.

The only difference I see is the array name and the range to read into it. But it throws a subscript out of range error.

Thanks,
Howard

Sub DV_Test()
Dim ValidationList() As Variant, i As Integer
ValidationList = Range("F1:F10")

' Dim Arr() As Variant
' Arr = Range("A1:A10")

For i = 0 To UBound(ValidationList)
ValidationList(i) = i + 1
Next

With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=Join(ValidationList, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
 
C

Claus Busch

Hi Howard,

Am Fri, 23 May 2014 17:13:47 -0700 (PDT) schrieb L. Howard:
' Arr = Range("A1:A10")
Click to expand...

arr is a 2D array

try:

Sub DV()
Dim myStr As String
Dim myArr As Variant
Dim arrDV() As Variant
Dim i As Long, j As Long

myArr = Range("F1:F10")

ReDim arrDV(Range("F1:F10").Cells.Count)
For i = LBound(myArr) To UBound(myArr)
arrDV(j) = myArr(i, 1)
j = j + 1
Next
myStr = Join(arrDV, ",")

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:=myStr
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Or write the array in a string at once:
Sub DV_2()
Dim myStr As String
Dim myArr As Variant
Dim i As Long

myArr = Range("F1:F10")

For i = LBound(myArr) To UBound(myArr)
myStr = myStr & myArr(i, 1) & ","
Next
myStr = Left(myStr, Len(myStr) - 1)

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myStr
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Sat, 24 May 2014 09:07:17 +0200 schrieb Claus Busch:

there is a typo in Sub DV
ReDim arrDV(Range("F1:F10").Cells.Count)
Click to expand...
ReDim arrDV(Range("F1:F10").Cells.Count - 1)

because arrDV is a 1D array and starts with 0

Regards
Claus B.
 
L

L. Howard

Hi Howard,



Am Sat, 24 May 2014 09:07:17 +0200 schrieb Claus Busch:



there is a typo in Sub DV




ReDim arrDV(Range("F1:F10").Cells.Count - 1)



because arrDV is a 1D array and starts with 0



Regards

Claus B.

--
Click to expand...


Works a treat, as usual.

I swear, arrays will be the death of me!

Thanks Claus.
 
G

GS

Other than the obvious exercise in array handling this demonstrates,
I'm curious why you don't just assign Range("F1:F10") as the DV source
list for Range("A1:A10") in one shot! What am I missing here?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
L

L. Howard

Other than the obvious exercise in array handling this demonstrates,

I'm curious why you don't just assign Range("F1:F10") as the DV source

list for Range("A1:A10") in one shot! What am I missing here?
Click to expand...


Hi Garry,

Here is the question I was responding to in my first post here.
Regarding the creation of a list (Insert or delete a drop-down list - Excel), is it possible to do this as a macro?
Click to expand...


After posting Claus's solution to my query here, this is the next question I am trying to respond to.
Is it possible to create the drop down values within VB, or will I alwayshave to reference cells in a worksheet?
Click to expand...

Followed by this.
How could I tweak this to populate a column in specific worksheets for rows that have values? I'm using a data connection to get data from an external file and have set the properties to insert cells for new data, delete unused cells. After the data refresh is complete I'd like to apply your solution for each worksheet in column 'S' for any rows that have data.
Click to expand...

For the "create the drop down values within VB..." question, here is where I am so far with an Object-Defined error.

Sub DV_Test()

Dim MyArray As Variant

MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

'With Range("A1:A5").Validation '/ Puts DV's in A1 to A5 all with F1:F10 list.
With Range("B1").Validation '/ Puts DV in B1 with the F1:F10 list.
'With Selection.Validation '/ Put DV in selected cell/s with the F1:F10list.
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=MyArray

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub


I have no idea what the last query is looking to do.

Howard
 
G

GS

Ok, Claus shows how to assign DV with VBA and so addresses that Q.
What's probably throwing the error is the assignment of the array. The
DV formula needs to be a range or a delimited list. This can be done
using the Join() function...

<snip>
Formula1:=Join(MyArray, ",")

...so this works...

Sub InsertDV()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=Join(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), ",")
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With
End Sub


I wasn't seeing the connection between the array being assigned and
your imported data (assuming there was a connection). I also was
curious about the data source (external file) and how it was being
retrieved, what 'properties' you're setting, etc.!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
L

L. Howard

Ok, Claus shows how to assign DV with VBA and so addresses that Q.

What's probably throwing the error is the assignment of the array. The

DV formula needs to be a range or a delimited list. This can be done

using the Join() function...



<snip>

Formula1:=Join(MyArray, ",")



..so this works...



Sub InsertDV()

With Selection.Validation

.Delete

.Add Type:=xlValidateList, _

AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, _

Formula1:=Join(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), ",")

.IgnoreBlank = True: .InCellDropdown = True

.ShowInput = True: .ShowError = True

End With

End Sub





I wasn't seeing the connection between the array being assigned and

your imported data (assuming there was a connection). I also was

curious about the data source (external file) and how it was being

retrieved, what 'properties' you're setting, etc.!
Click to expand...

Hi Garry,

That works great. Thanks.

My first thought to using code to produce a DV drop down was the drop down list would surely be data somewhere on the sheet. Just need a way to get it into the 'source window'. I was thinking why would you want to hard codeyour source list and have to re-enter it into the code each time you wanted a DV.

Still kinda wondering why.

I don't know if I will ever figure out or understand that last paragraph relating to column S where any row has data.

Thanks again.
Howard
 
C

Claus Busch

Hi Garry,

Am Sat, 24 May 2014 22:25:03 -0400 schrieb GS:
Ok, Claus shows how to assign DV with VBA and so addresses that Q.
What's probably throwing the error is the assignment of the array. The
DV formula needs to be a range or a delimited list. This can be done
using the Join() function...
Click to expand...

I wanted to show Howard how to work with this array.
If I have a range I don't read it in an array for DV. I give this range
a name and use the name as source for DV or use the range directly:
Sub DV1()

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$F$1:$F$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

or:
Sub DV2()

ActiveWorkbook.Names.Add Name:="MyList", _
RefersTo:="=Sheet1!F1:F10"

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=MyList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


Regards
Claus B.
 
G

GS

Hi Claus,
This I understand, and it makes good sense! The only reason to 'hard
code' refs is when they'll be constant, otherwise it's not good idea! I
think what Howard needed here is correct syntax for either a delimited
string OR range ref. Your examples demo this nicely and so I expect
Howard is happy that his Q is answered!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the Empty Cell Macro 9
Macro to set new data validation each time 2
VBA triggers on tabkey 8
Validation-List created with VBA 1
Help with text length of cells 4
i to = column in Formula 19
Data Validation List Problem 1
Many Tried It Byt Can U... 4

Top