Read a range into an array
 

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

Read a range into an array
 

Hi Howard,

Am Fri, 23 May 2014 17:13:47 -0700 (PDT) schrieb L. Howard:

' Arr = Range("A1:A10")

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Read a range into an array
 

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)
ReDim arrDV(Range("F1:F10").Cells.Count - 1)

because arrDV is a 1D array and starts with 0

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Read a range into an array
 

On Saturday, May 24, 2014 12:15:23 AM UTC-7, Claus Busch wrote:
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)

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



because arrDV is a 1D array and starts with 0



Regards

Claus B.

--


Works a treat, as usual.

I swear, arrays will be the death of me!

Thanks Claus.

Read a range into an array
 

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

Read a range into an array
 

On Saturday, May 24, 2014 1:05:32 PM UTC-7, GS wrote:
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


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?


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 always have to reference cells in a worksheet?

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.

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:F10 list.
.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

Read a range into an array
 

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

Read a range into an array
 

On Saturday, May 24, 2014 7:25:03 PM UTC-7, GS wrote:
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



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 code your 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

Read a range into an array
 

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...

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Read a range into an array
 

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