VB.Net pulling dates from Excel into Array

Question

I've been having loads of trouble pulling dates from excel and loading them into an array. I have code that should, for all intents and purposes work, but nothing is being loaded into the array. When I do a search in the array for a value, no matter what it is, it says it has been found at Index -1 in the array. Code is as follows.

Dim d As String
Dim strDate(0 To 35) As String
Dim dCell As Object
Dim tDate As String = Now.ToShortDateString

    Dim dCount = WFMBook.Range("G2:AO2").Cells.Count
        For y = 1 To dCount Step +1
            For Each dCell In WFMBook.Range("G2:AO2").Cells(y).Value.ToString
                d = WFMBook.Range("G2:AO2").Cells.Value.ToString
            Next
            strDate(y) = d
            TextBox1.Text = strDate(0)
        Next

And then after all the data is supposedly loaded into the array (I have the textbox function to check whether things are in the array -- no results are printed into the textbox.) I perform this function:

Dim dindex As Integer = Array.FindIndex(strDate, Function(s2) s2 = tDate)
MsgBox("Found Date " & tDate & " at index " & dindex)

As I said previously, though, the MsgBox shows that it was found at index -1, and no array results are ever printed to the textbox. I believe it has something to do with Excel Date/Time somehow. How can I get this to properly load the dates into a string format ex "12/3/2015" into an array? Thanks!

You can use the Locals window and set a break point. Step through the For y loop. I think your problem is in 'For Each dCell In WFMBook.Range' What are you trying to do there? — peege
– peege, Commented Jan 4, 2015 at 8:08
I'm trying to load each cell in that range into the array. It has worked previously. — AbsoZed
– AbsoZed, Commented Jan 5, 2015 at 10:59

Jason Faulkner · Accepted Answer · 2015-01-04 16:59:12Z

1

It appears your problem is you are not iterating across your range correctly. You just need to reference the Cells property respective to your test range of WFMBook.Range("G2:AO2").

Dim checkRange = WFMBook.Range("G2:AO2")
Dim dCount = checkRange.Cells.Count

For y = 1 To dCount
    ' Row postion (1) remains the same, but the column is incremented with y.
    d = checkRange.Cells(1, y).Value.ToString
    strDate(y) = d
    TextBox1.Text = strDate(0)
Next

answered Jan 4, 2015 at 16:59

Jason Faulkner

6,6682 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.Net pulling dates from Excel into Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related