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 |
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 |
All times are GMT +1. The time now is 08:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com