Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
list box
I have successfully populated "Frm_ValueStore.lst_Cols" with 6 intergers;
"Frm_ValueStore.lst_Rows" with 4 intergers. This form is hidden, but still loaded. On a second form, text box "txt_CellSelection" is populated with the address of where the user just right clicked. I want to test to see if the column & row the user right click into are in either "Frm_ValueStore.lst_Cols" or "Frm_ValueStore.lst_Rows", respectively. In the code below, Frm_ValueStore.lst_Cols.ListCount is correctly evaluating to "6". I am getting runtime error 9, Subscript out of range on "var(i)" Thanks in advance for your help! Private Sub cmd_Spread_Click() Dim i As Integer Dim j As Integer Dim Total_Test As Boolean Dim var As Variant 'Test to see if cell is on a subtotal column Total_Test = False var = Frm_ValueStore.lst_Cols.List For i = 0 To Frm_ValueStore.lst_Cols.ListCount - 1 If var(i) = Range(txt_CellSelection).Column Then Total_Test = True Next i Redim var var = Frm_ValueStore.lst_Rows.List For i = 1 To Frm_ValueStore.lst_Rows.ListCount If var(i) = Range(txt_CellSelection).Row Then Total_Test = True Next i Debug.Print Total_Test Unload frm_Spread End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
list box
You can use 0 for a column reference as a relative reference when using
something like Offset(1, 0), but it will not work in an absolute reference as you are using with Var(i) = Something.Column. You can try: If var(i + 1) = Range(txt_CellSelection).Column Then Total_Test = True That would then start with the positive value of one. "Cinque Terra" wrote: I have successfully populated "Frm_ValueStore.lst_Cols" with 6 intergers; "Frm_ValueStore.lst_Rows" with 4 intergers. This form is hidden, but still loaded. On a second form, text box "txt_CellSelection" is populated with the address of where the user just right clicked. I want to test to see if the column & row the user right click into are in either "Frm_ValueStore.lst_Cols" or "Frm_ValueStore.lst_Rows", respectively. In the code below, Frm_ValueStore.lst_Cols.ListCount is correctly evaluating to "6". I am getting runtime error 9, Subscript out of range on "var(i)" Thanks in advance for your help! Private Sub cmd_Spread_Click() Dim i As Integer Dim j As Integer Dim Total_Test As Boolean Dim var As Variant 'Test to see if cell is on a subtotal column Total_Test = False var = Frm_ValueStore.lst_Cols.List For i = 0 To Frm_ValueStore.lst_Cols.ListCount - 1 If var(i) = Range(txt_CellSelection).Column Then Total_Test = True Next i Redim var var = Frm_ValueStore.lst_Rows.List For i = 1 To Frm_ValueStore.lst_Rows.ListCount If var(i) = Range(txt_CellSelection).Row Then Total_Test = True Next i Debug.Print Total_Test Unload frm_Spread End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
create new list from list A, but with exclusions from a list B | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |