Question Daily readings and Excel

[collegegirl7]

I have an application in which I am using vb.net and excel. Daily readings are taken, written into vb.net and then saved into excel. At the moment i have it set so that:

If Gdate = "12 February 2010" Then


.Range("C4").Value = TextBox1.Text
.Range("C5").Value = TextBox2.Text
.Range("C6").Value = TextBox3.Text
.Range("C7").Value = TextBox4.Text
.Range("C8").Value = TextBox5.Text
.Range("C10").Value = TextBox6.Text
.Range("C11").Value = TextBox7.Text
.Range("C12").Value = TextBox8.Text
.Range("C13").Value = TextBox9.Text
.Range("C14").Value = TextBox10.Text
.Range("C16").Value = TextBox11.Text
.Range("C17").Value = TextBox12.Text
.Range("C18").Value = TextBox13.Text
.Range("C19").Value = TextBox14.Text
.Range("C20").Value = TextBox15.Text
.Range("C21").Value = TextBox16.Text
.Range("C22").Value = TextBox17.Text
.Range("C24").Value = TextBox18.Text
.Range("C25").Value = TextBox19.Text
.Range("C26").Value = TextBox20.Text

End if

At the moment, if the date is the 12th of February the inforation goes into the following cells. I want to be able to say that if the date is the 13th of February that all the data gets added into same cells in column E without having to do

If Gdate = "13 February 2010" Then


.Range("E4").Value = TextBox1.Text
.Range("E5").Value = TextBox2.Text
.Range("E6").Value = TextBox3.Text
.Range("E7").Value = TextBox4.Text

etc. Is there a way to do this without having to type it all out manually as it will be over the next year i will need to fill in this data.

Thanks.

 

[JohnH]

DateTime.DayOfYear Property (System) then it looks like worksheet.Rows(RowIndex,ColumnIndex) can be used.

 

[collegegirl7]

I am new to this and I am not sure what you mean? I have tried following the example but am finding it difficult to understand. Thanks.

 

[JohnH]

What it means is, if you have a Date value you can use the DayOfYear property to get the column index you need. Date.Now for instance, is the Date value of the current day.

 

[collegegirl7]

Thank you. I have tried:

Dim column As String = "C"
Dim Gdate As Date = "01 January 2010"
Dim todayDate As Date = DateTime.Now

Do

column = Chr(Asc(column) + 2)
Gdate = Gdate.AddDays(1)


Loop Until Gdate = todayDate

but it doesnt work.

 

[JohnH]

I have no idea what you mean by that, but try this:

Dim d As Date = Date.Now
Dim offset As Integer = d.DayOfYear - 4       
Do Until d.Year = 2011
    d = d.AddDays(1)
    CType(sheet.Cells(d.DayOfYear - offset, 1), Excel.Range).Value = d.ToShortDateString
Loop