-
Mar 12th, 2014, 07:07 AM
#1
Thread Starter
Addicted Member
retrive specific elements from 2d arrays
Code:
Dim array As Array = value_range.Value2
MsgBox(value_range.Value2(0, 1))
kepping getting an Index was outside the bounds of the array at message box when i try the above code
-
Mar 12th, 2014, 08:09 AM
#2
Re: retrive specific elements from 2d arrays
Put a breakpoint on the line with the MsgBox. When execution stops at the breakpoint, take a look at array (and while you're at it, give it a different name. Nothing good can come of having a variable named array), or at value_range.Value2. What is the size of the array? What are the sizes of the dimensions? They aren't going to be what you are expecting, but beyond that nobody can say why because we don't know where that thing came from.
My usual boring signature: Nothing
-
Mar 12th, 2014, 08:46 AM
#3
Thread Starter
Addicted Member
Re: retrive specific elements from 2d arrays
my code reads an excel file line by line so at array it has the values from cell a2 and b2 in excel file. the excel sheet has 20 entries but only reads 1 line at a time
-
Mar 12th, 2014, 08:50 AM
#4
Re: retrive specific elements from 2d arrays
Is value_range.Value2 really a 2D array? If it is then the IDE would complain on the 1st code line already because array is declared as 1D array (Dim array As Array = value_range.Value2).
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
- Abraham Lincoln -
-
Mar 12th, 2014, 08:57 AM
#5
Thread Starter
Addicted Member
Re: retrive specific elements from 2d arrays
sorry its a 1d array with 2 values been read in. i need to strip out one of the values and get that to match something else so that i copy over what i want to excel. they way my code is working is it copies over everything from one excel to another
-
Mar 12th, 2014, 09:05 AM
#6
Thread Starter
Addicted Member
Re: retrive specific elements from 2d arrays
this is the code for array
Code:
For c As Integer = 2 To 100
Dim value_range As Excel.Range = objSht.Range("A" & c, "S" & c)
' Dim a As Object
Dim array As Array = value_range.Value2
MsgBox(value_range.Value2(0, 1))
' If value_range.Value2[0,1]= DateTimePicker1.Value Then
AddRow(array)
Else
End If
Next
-
Mar 12th, 2014, 09:48 AM
#7
Re: retrive specific elements from 2d arrays
If it's a 1D array, then you can't access it with (0,1). It kind of looks like you could, because there really shouldn't be much difference between that and accessing a 1D array, but there is. After all, you are accessing the second element of the first line of data. You only have one line of data, so it would make sense that you could do this, but if the compiler sees value_range.Value2 as being a 1D array, then it will balk at the way you have it written. In that case,
value_range.Value2(1)
would work.
My usual boring signature: Nothing
-
Mar 12th, 2014, 09:58 AM
#8
Re: retrive specific elements from 2d arrays
Which column in Excel is the date value you're looking for? Besides, Value2 returns only floating point values, so you will need to convert that back to datetime type before trying to test it against your datetimepicker.value. And as the type name suggest, it's date AND time, so unless the 2 datetime objects are equal to the tick, your test will fail. If you're interested in only the date portion of the datetime object then you need to strip the time off, i.e datetimepicker.value.Date will give you the date with the time being 0:00:00.
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
- Abraham Lincoln -
-
Mar 12th, 2014, 11:46 AM
#9
Thread Starter
Addicted Member
Re: retrive specific elements from 2d arrays
how to i convert the float to a datetime
Code:
Dim array As Array = value_range.Value2
MsgBox(value_range.Value2(1, 2))
If value_range.Value2(1, 2) = DateTimePicker1.Value.Date Then
AddRow(array)
Else
the value_range.Value2(1, 2) pull in the date like this 41638 want to convert that to the date it means which is the 30/12/2013
-
Mar 12th, 2014, 12:43 PM
#10
Re: retrive specific elements from 2d arrays
Originally Posted by rjhe22
how to i convert the float to a datetime
Code:
Dim array As Array = value_range.Value2
MsgBox(value_range.Value2(1, 2))
If value_range.Value2(1, 2) = DateTimePicker1.Value.Date Then
AddRow(array)
Else
the value_range.Value2(1, 2) pull in the date like this 41638 want to convert that to the date it means which is the 30/12/2013
Use Date.FromOADate method.
Code:
Dim d As Date = Date.FromOADate(41638)
MessageBox.Show(d.ToString)
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
- Abraham Lincoln -
-
Mar 12th, 2014, 02:29 PM
#11
Re: retrive specific elements from 2d arrays
Range.Value2 will return a two dimensional array if the Range is contains more than one cell. The array will be in a [Row][Col] format that matches the specified Range and the indices begin with one (1) not zero (0). This is analogous to the Range.Cells(row,col) usage in VBA.
The array will be an array of objects. As stated above, Range.Value2 will return a double type if the cell contains a numeric value, Nothing if the cell is empty, and a String for of course string values.
If the Cell if a formatted numeric (Date, Time, Currency, Accounting, etc.) and you use Range.Value, the following conversions are performed for you. Note that the array values are boxed in System.Object values.
- Numeric ==> System.Double
- Currency ==> System.Decimal
- Accounting ==> System.Decimal
- Date ==> System.DateTime
- Time ==> System.Double
- Percentage ==> System.Double
- Fraction ==> System.Double
- Scientific ==> System.Double
- Text ==> String if string value else System.Double if it is a number formatted as Text (Not a quoted number)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|