VBA 'sees' data that is not in cells

[Porschemaniac]

I wonder if someone can help me with this.

Currently, I am trying to build a spreadsheet that tracks training courses taken.

Part of this will be a 'stats' page, which the user and their manager can use to discuss progress against individual plans.

I need the facility to enter a 'last review date' and then interrogtae a list of dates (relating to training course completion dates) on another worksheet (called "Providers Mapping").


Basically, if the date in the list is equal to or greater than the last review date, I want to count that (against variable ctr) and if the dates in the list are less than the last review date then I need to count that also (variable ctra).

The VBA that I have already brought together for this is:

Private Sub CommandButton1_Click()
Dim Prompt As String
Dim Caption As String
Dim Defval As Date
Dim Cell As Range
Dim CurDate As Date
Dim Lrd As Date
Dim ctr As Integer
Dim ctra As Integer
Dim CellDate As Date
Dim Cellct As Integer

CurDate = Date
ctr = 0
ctra = 0
CellDate = 0

Defval = Date - 30

Prompt = "What date was your last review? Please enter as DD/MM/YYYY"
Caption = "Please enter the last review date (DD/MM/YYYY)"


Lrd = InputBox(Prompt, Caption, Defval)

If Lrd = vbCancel Then Exit Sub

For Each Cell In Worksheets("Providers Mapping").Range("I3:I394")
Cellct = Cellct + 1
CellDate = Cell.Value
Debug.Print "Active Cell Value " & CellDate & " Todays Date is " & CurDate
If CellDate >= Lrd Then
ctr = ctr + 1
End If
If CellDate < Lrd Then
ctra = ctra + 1
Debug.Print "Active Cell Value " & CellDate & " Todays Date is " & CurDate
End If
Next Cell

Worksheets("Stats").Range("B6") = "You have completed " & ctr & " training activities since your last review."

Worksheets("Stats").Range("B8") = "In the period before your last review, you have completed " & ctra & " training activities."

Worksheets("Stats").Range("B11") = "Total of training activties available is " & Cellct

Worksheets("Stats").Range("B14") = "Todays Date is " & CurDate
End Sub

The Input box seems to work fine, but the counts are way off.

As you can see, I have included a Debug.Print to 'dump' the variable values into the Immediate window. This highlights two issues:

- The list of items in the Immediate box is only about 190 lines long, whereas the list of dates in the "Providers Mapping" sheet is about 390 rows long.

- To make life easy, I have set all of the dates in the "Providers Mapping" sheet to 05/11/2009, however, the dates 'dumped' into the "Immediate" window includes dates not on the sheet - these appear to be historic values that previously populated the cells.

It's really got me scratching my head, so any help that anyone can offer will be greatly appreciated.

Thanks,

Ian.

 

[HarleyQuinn]

At first glance:

- The list of items in the Immediate box is only about 190 lines long, whereas the list of dates in the "Providers Mapping" sheet is about 390 rows long.

I'm pretty certain the Immediate windows only holds the last 199 lines written to it.

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[SkipVought]

Hi,

Can you post some specific examples of the values in the immediate window and the corresponding values from the worksheet?

I'd change the Debug.Print to...

Debug.Print "Cell Value " & CellDate & "  Todays Date is " & CurDate & " Row " & cell.row

BTW, you MAY only report the Active Cell value ONE TIME in your loop.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Hi and thanks for the replies,

Skip, adding the row number to the Debug Print highlighted a problem - it was starting at about 200.

In turn this prompted me to look more closely at the "Providers Mapping" sheet (which had been provided to me by someone esle). Becuase, I have had a bit of tunnel vision on the date column, I had not paid great attention to the left hand side of the sheet. When I did, I noticed that they, for some reason, had 'collapsed' numerous rows!!! Once I opened these out, the rogue dates appeared.

Thanks very much for the tip - BTW, do you know why the Debug Print rows would begin at 200, or thereabouts?

Thanks again for your help.

Ian.

 

[SkipVought]

Your Immediate window has a capacity of about 200 lines. Previously written data rolls off the display.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

do you know why the Debug Print rows would begin at 200, or thereabouts?

I'd say its' because

the Immediate windows only holds the last 199 lines written to it.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Porschemaniac]

Doh!!

 

[SkipVought]

FYI, faq707-4594 might also be helpful.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}