Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Using CountA


Reply
 
Thread Tools Display Modes
  #1  
Old 05-04-2004, 06:05 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Default 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
Reply With Quote
  #2  
Old 05-04-2004, 09:27 PM
Kluz's Avatar
Kluz Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,335
Default

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
Reply With Quote
  #3  
Old 05-04-2004, 10:10 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Default 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
Reply With Quote
  #4  
Old 05-05-2004, 01:26 AM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Wink 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
Reply With Quote
  #5  
Old 05-05-2004, 05:03 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Unhappy 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
Reply With Quote
  #6  
Old 05-05-2004, 08:24 PM
Kluz's Avatar
Kluz Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,335
Default

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
Reply With Quote
  #7  
Old 05-05-2004, 10:02 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Question 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
Reply With Quote
  #8  
Old 05-05-2004, 11:08 PM
tboltfrank tboltfrank is offline
Senior Contributor
 
Join Date: Jul 2003
Posts: 1,022
Default

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.
Reply With Quote
  #9  
Old 05-05-2004, 11:43 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Red face 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.
Reply With Quote
  #10  
Old 05-06-2004, 02:17 AM
XL-Dennis XL-Dennis is offline
Banned
 
Join Date: Mar 2003
Location: Östersund Sweden
Posts: 908
Default

Kluz - In other part of the world the alphabet have more then 26 letters
Reply With Quote
  #11  
Old 05-06-2004, 05:00 PM
Kluz's Avatar
Kluz Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,335
Default

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
Reply With Quote
  #12  
Old 05-06-2004, 05:46 PM
Mitchys's Avatar
Mitchys Mitchys is offline
Centurion
 
Join Date: Apr 2004
Location: Australia
Posts: 115
Thumbs up 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
Reply With Quote
  #13  
Old 05-06-2004, 07:53 PM
Kluz's Avatar
Kluz Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,335
Default

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding an object from a Class Collection to another Class Collection daffersd Excel 18 02-16-2004 12:21 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->