 |
 |
05-04-2004, 06:05 PM
|
 |
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Using CountA
|
G'day guys and gals,
I want to add all non-blank cells within a range. My code thus far filters the raw data for all entries between two dates, copies, then pastes the filtered data into a worksheet.
There are two problems. 1. First of all, the following CountA only returns the value 1 rather than the count of all non-blank cells:
Code:
intLastRow = Sheets("Frtnghtly Rprt").Cells.SpecialCells(xlLastCell).Row
Range("H" & intLastRow).Select
ActCel = ActiveWindow.RangeSelection.Address
Range(ActCel).Offset(1, 0).Select
intCountAH = WorksheetFunction.CountA("H1:" & ActCel)
ActiveCell = intCountAH
Any suggestions on what I am doing wrong here?
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):
Code:
Cells.Select
Selection.ClearContents
Any hints would be appreciated,
Mitchys |
|
05-04-2004, 09:27 PM
|
 |
Sapience.Aquire
Super Moderator
* Expert *
|
|
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,306
|
|
Firstly: here is a better way to find the last cell with a value in a column.
Secondly: you do not need to select a cell to work with it:
Code:
ActCel = Range("H" & intLastRow).Address
Thirdly: since you have not specified which sheet to apply the CountA function to, it applies it to the Activesheet which may not be sheet Frtnghtly Rprt. |
__________________
No the other right mouse click
|
05-04-2004, 10:10 PM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Still Struggling
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:
Code:
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
' ==========================================================
Sheets("Frtnghtly Rprt").Activate
ActiveSheet.Paste
H = LastRowInColumn(3)
ActCelH = Range("H" & H).Address
Range("H1:" & ActCelH).Select
intCountAH = WorksheetFunction.CountA("H1:" & ActCelH)
Range(ActCelH).Offset(1, 0).Select
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
Sheets("Training").Select
Selection.AutoFilter
Range("A1").Select
' 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
End If
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.
Thanks,
Mitchys |
|
05-05-2004, 01:26 AM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Found it!
Well, thanks for any who have been trying to solve this for me. After a lot of searching, I found that I was incorrectly using the CountA function.
Here is the code that works - incase anyone else has a similar problem:
Code:
Dim ActCelH As Variant
Dim H As Integer
H = LastRowInColumn(3)
ActCelH = Range("H" & H).Address
Range(ActCelH).Offset(1, 0) = Application.CountA(Range("H1:" & ActCelH))
All I was missing was placing the {"H1:" & ActcelH} within Range(). After taking Kluz' comments on board, I was also able to stop the incrementing of the last cell.
Thanks,
Mitchys  |
|
05-05-2004, 05:03 PM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Finding last row doesn't always work
G'day Kluz,
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.
Quote:
|
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):
Code:
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
Do you have any idea why this is happening?
Thanking you in advance,
Mitchys  |
|
05-05-2004, 08:24 PM
|
|
Sapience.Aquire
Super Moderator
* Expert *
|
|
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,306
|
|
Don't kick yourself too hard, but last time I checked there were 26 letters in the alphabet:
Code:
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.  |
__________________
No the other right mouse click
|
05-05-2004, 10:02 PM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
I must be having a mind blank!
Quote:
|
Originally Posted by Kluz
Don't kick yourself too hard, but last time I checked there were 26 letters in the alphabet:
Code:
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.
Sorry to be a pain!
Regards,
Mitchys  |
|
05-05-2004, 11:08 PM
|
|
Senior Contributor
|
|
Join Date: Jul 2003
Posts: 1,022
|
|
Mitchys,
At the very least, your code is very confusing.
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 02:12 AM.
|
05-05-2004, 11:43 PM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Comprendez!
Thanks for your help tboltfrank!
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.
Regards,
Mitchys |
Last edited by Mitchys; 05-06-2004 at 01:19 AM.
|
05-06-2004, 02:17 AM
|
|
Banned
|
|
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
|
|
Kluz - In other part of the world the alphabet have more then 26 letters  |
|
05-06-2004, 05:00 PM
|
|
Sapience.Aquire
Super Moderator
* Expert *
|
|
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,306
|
|
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:
Code:
Y = LastRowInColumn(20)
'additional code
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. |
__________________
No the other right mouse click
|
05-06-2004, 05:46 PM
|
|
Centurion
|
|
Join Date: Apr 2004
Location: Australia
Posts: 115
|
|
Simple Answer
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.
Thanks again for your input.
Regards,
Mitchys |
|
05-06-2004, 07:53 PM
|
|
Sapience.Aquire
Super Moderator
* Expert *
|
|
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,306
|
|
|
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.
|
__________________
No the other right mouse click
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
|
|
 |
|
