2. Next, each time I run the procedure, intLastRow is one number greater than the previous run - even though I delete all cell entries at the start of this procedure (the worksheet this copies filtered data to needs to be completely written-over each time). In other words, if I run the procedure over and over again using the same dates, the ActCel keeps moving down the sheet in increments of one cell each time. Here is the code for deleting all entries, which appears right after the sheet is activated (3rd line of code for this procedure):
Thank you Kluz for your advice - suggestions one and two have been taken on board and appear to be working fine.
Here is a bit more of my code, so you can see that I am activating the sheet where I want the CountA to function:
If Response = vbYes Then
Sheets("Frtnghtly Rprt").Activate 'Goto the report spreadsheet.
'Get the date start from the user.
DateStart = InputBox("Enter the first date of the fortnight to be reported")
'Get the date end from the user.
DateEnd = InputBox("Enter the last date for the fortnight to be reported")
Range("A1").Value = DateStart 'Paste the date start in A1
Range("B1").Value = DateEnd 'Paste the date end in B1
Range("C1").Select 'Set the active cell at C1.
Sheets("Training").Activate 'Go back to the Training sheet.
Range("A2").Select 'Set the active cell on the first line of data.
Selection.AutoFilter 'Set the Auto Filter on
Selection.CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CDbl(DateStart), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(DateEnd) 'Filter the data according to the Start and End dates
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'Highlight the filtered cells
Selection.Copy 'Copy the selected cells
H = LastRowInColumn(3)
ActCelH = Range("H" & H).Address
Range("H1:" & ActCelH).Select
intCountAH = WorksheetFunction.CountA("H1:" & ActCelH)
ActiveCell = intCountAH
' these lines were added so I could get a message box with the variables I am working with.
' Still, intCountAH comes up as "1".
Dim MyString1 As String
Dim MyString2 As String
Dim MyString3 As String
Dim MyString4 As String
MyString1 = "ActCel = "
MyString2 = "H = "
MyString3 = "intCountAH = "
MyString4 = " "
MsgBox MyString1 & ActCelH & MyString4 & MyString2 & H & MyString4 & MyString3 & intCountAH
' Re-set variables to zero or null
Msg = ""
Style = ""
Title = ""
Response = ""
DateStart = 0
DateEnd = 0
ActCelH = 0
intCountAH = 0
MyString1 = ""
MyString2 = ""
MyString3 = ""
MyString4 = ""
H = 0
If you could give me some advice on the CountA function, I would greatly appreciate it. The code between the "==========" is where I think the problems are.
The thread you directed me to (see below) works quite fine - until the columns go beyond Z. I have two columns with data in AA and AB, and the last row for these is reported to be 1 and 4, respectively (found when I produced a MsgBox and listed the values for each variable) - even though all the other columns correctly places the Sum or Count in row 5.
Originally Posted by Kluz
Firstly: here is a better way to find the last cell with a value in a column.
Here is my code (I have only listed the entries for Y - AB simply to save space):
Dim ActCelY as Variant
Dim Y As Integer
Dim ActCelZ As Variant
Dim Z As Integer
Dim ActCelAA As Variant
Dim AA As Integer
Dim ActCelAB As Variant
Dim AB As Integer
Y = LastRowInColumn(20)
Z = LastRowInColumn(21)
AA = LastRowInColumn(22)
AB = LastRowInColumn(23)
MsgBox "Z " & Z & " " & "AA " & AA & " " & "AB " & AB
ActCelY = Range("Y" & Y).Address
ActCelZ = Range("Z" & Z).Address
ActCelAA = Range("AA" & AA).Address
ActCelAB = Range("AB" & AB).Address
Don't kick yourself too hard, but last time I checked there were 26 letters in the alphabet:
MsgBox "Column number for Y: " & Range("Y:Y").Column & vbNewLine & _
"Column number for Z: " & Range("Z:Z").Column & vbNewLine & _
"Column number for AA: " & Range("AA:AA").Column & vbNewLine & _
"Column number for AB: " & Range("AB:AB").Column
It happens to everyone at sometime.
I'm not sure how this applies to my code - the message box is not part of my code, but only put there for me to see what row my code was assuming to be the last row. For all columns from C to Z, the code correctly places the Sum() or CountA() in the right row; however, for columns AA and AB, it is not - and actually inputs a figure that overwrites the filtered data.
Case and point: You have seemingly very illogical variable's... such as: AA = LastRowInColumn(22), but Column(AA) is actually Column(27). - Both that, and the fact that you indicated having trouble with your code, when it refers to Column(AA) and (AB), I'm pretty sure led Kluz to remind you, that there's 26 letters, in the alphabet.
Edit: Is there some reason, that you see your variable's as being best, that way??
Edit: Corrected some spelling typo's
Last edited by tboltfrank; 05-06-2004 at 03:12 AM.
Partly, this is due to my being very new to vba, and I'm still working my way through understanding what the code is doing (i.e. that the number in the LastRowInColumn() referred to the column number. Now that you have pointed it out, it seems quite obvious!! )
I have taken your points on board and have made some changes to my code.
Mitchys: Didn't mean to confuse you with that code, it wasn't meant to be used within your specific application but to demonstrate that when you used:
Y = LastRowInColumn(20)
ActCelY = Range("Y" & Y).Address
you were sending the column number (20) for column "T" then using the returned row number to access the address for that row in column "Y" (26). From the code that you supplied that was the only source of potential error that I could see that might result in values being put into the wrong row.
Once I worked out what the LastRowInColumn(20) referred to, I noticed that not all cells within a row had entries. So, as you said, it was placing the CountA result in the wrong column.
As the first column in each sheet I'm transfering data from will always have an entry, I now use just LastRowInColumn(3) to find the last row with data. (NB the column is 3 only because when I paste the data into the new sheet, I have it pasting from C1 as I have data in A1 and B1.)
And as you can imagine, I don't have so many variables to work with so my code is much more stream-lined.
The project I'm working on is producing a menu-based data entry workbook to enter information from different reports. The questions I have posed on the Forum so far have had to do with taking data from different sheets to produce weekly, fortnightly (bi-weekly), monthly and quarterly reports.
Glad you've got it worked out. The use of the same row number for all entries is definately a smart move to reduce code, speed up the process and also use a bit less RAM. Amazing how a project will build up in complexity. Sounds like your corp will save a great deal of time once your work is implemented.