Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Read a Range into an Array | Excel Programming | |||
Read range to array | Excel Programming | |||
How do i read Selection.Shapes.Range(Array(i)? | Excel Programming | |||
Read a range to an array | Excel Programming | |||
Read Range Data into Array | Excel Programming |