referring cells of sheet 1

[glennboll]

hello I'm making a line graph with data on sheet 1. The problem is that I need only the data of today (20-03-2011) But I don't know how to select these cells of sheet1. This is the code I have.


code

Dim lrow, cRow, vandaag, vandaag2 As String
Dim j, x, y, z, tot As Integer
Dim myChartObject As ChartObject
Dim myChart As Chart





vandaag = CStr(Now)
vandaag = Mid(vandaag, 1, 11)



ActiveSheet.Shapes.AddChart.Select

ActiveChart.ChartType = xlLine

ActiveChart.SeriesCollection.NewSeries

ActiveChart.ChartType = xlLine

ActiveChart.SeriesCollection.NewSeries

lrow = sheets("blad1").Range("B3:B4").End(xlDown).row

ActiveChart.SeriesCollection(1).Name = Date




For j = 1 To lrow

vandaag2 = Cells(2 + j, "B")

vandaag2 = Mid(vandaag2, 1, 11)
If vandaag2 = Date Then x = x + 1


Next

If lrow > 4221 Then
If x > 4221 Then
z = x + 1

Else
z = x
End If
Else
z = x + 1
End If


For y = 1 To z
ActiveChart.SeriesCollection(1).Values = "=blad1!$A$3:$A$" & y
ActiveChart.SeriesCollection(1).XValues = "blad1!$B$3:$B$" & y
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
ActiveChart.AutoScaling = True
Next y

Selection.Top = 50
Selection.Width = 400
Selection.Height = 350
Selection.Left = 30




End Sub

 

[twinpowr]

Hi,

I have not tried the code you have posted.

It seems to me, you could filter the data to a new sheet for the date range you require, and then run your code from the filtered data.

Perhaps you could post some sample data, so we can have a look and test the code.

Ta

Ally.

 

[glennboll]

<table border="0" cellpadding="0" cellspacing="0" width="301"><col style="width: 93pt;" width="124"> <col style="width: 133pt;" width="177"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 93pt;" width="124" height="20">404</td> <td class="xl64" style="width: 133pt;" width="177">20-03-2011 13:28:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">403</td> <td class="xl64">20-03-2011 13:27:59</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">402</td> <td class="xl64">20-03-2011 13:27:58</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">401</td> <td class="xl64">20-03-2011 13:27:58</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">400</td> <td class="xl64">20-03-2011 13:27:28</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">399</td> <td class="xl64">20-03-2011 13:27:27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">398</td> <td class="xl64">20-03-2011 13:27:26</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">397</td> <td class="xl64">20-03-2011 13:27:25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">396</td> <td class="xl64">20-03-2011 13:27:24</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">395</td> <td class="xl64">20-03-2011 13:27:23</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">394</td> <td class="xl64">20-03-2011 13:27:22</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">393</td> <td class="xl64">20-03-2011 13:27:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">392</td> <td class="xl64">20-03-2011 13:27:20</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">391</td> <td class="xl64">20-03-2011 13:27:19</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">390</td> <td class="xl64">20-03-2011 13:27:18</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">389</td> <td class="xl64">20-03-2011 13:27:17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">388</td> <td class="xl64">20-03-2011 13:27:16</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">387</td> <td class="xl64">20-03-2011 13:27:15</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">386</td> <td class="xl64">20-03-2011 13:27:14</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">385</td> <td class="xl64">20-03-2011 13:27:13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">384</td> <td class="xl64">20-03-2011 13:27:12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">383</td> <td class="xl64">20-03-2011 13:27:11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">382</td> <td class="xl64">20-03-2011 13:27:10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">381</td> <td class="xl64">20-03-2011 13:27:09</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">380</td> <td class="xl64">20-03-2011 13:27:08</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">379</td> <td class="xl64">20-03-2011 13:27:07</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">378</td> <td class="xl64">20-03-2011 13:27:06</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">377</td> <td class="xl64">20-03-2011 13:27:05</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">376</td> <td class="xl64">20-03-2011 13:27:04</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">375</td> <td class="xl64">20-03-2011 13:27:03</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">374</td> <td class="xl64">20-03-2011 13:27:02</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">373</td> <td class="xl64">20-03-2011 13:27:01</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">372</td> <td class="xl64">20-03-2011 13:27:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">371</td> <td class="xl64">20-03-2011 13:26:59</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">370</td> <td class="xl64">20-03-2011 13:26:58</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">369</td> <td class="xl64">20-03-2011 13:26:55</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">368</td> <td class="xl64">20-03-2011 13:26:54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">367</td> <td class="xl64">20-03-2011 13:26:54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">366</td> <td class="xl64">20-03-2011 13:25:47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">365</td> <td class="xl64">20-03-2011 13:25:46</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">364</td> <td class="xl64">20-03-2011 13:25:46</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">363</td> <td class="xl64">20-03-2011 13:25:42</td> </tr> </tbody></table>

 

[glennboll]

the numers are my count values want i want is to count the cells with the same date for example 44 cells. For tomorrow I have 4 cells with the date of tomorrow