Application.CountA issue

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Hi All

Code:
 'Check Range
                        'Get last row to check
                        lRowEnd = WS.Range("B" & Rows.Count).End(xlUp).Row
            
                        'Check for values in F:N
                        For R = 12 To lRowEnd
                            If LCase$(WS.Cells(R, "B").Text) <> "total" Then
                         For C = 6 To 12 'Cols F:N                              
                         If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Then 'Copy row to Summary
    
                                        lRowTo = lRowTo + 1
                                        With wsSumm
                                            .Rows(lRowTo).Value = WS.Rows(R).Value
                                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                                        End With
                                        Exit For
                                    End If

I have the above code which copies the row if any data is within columns F:L The current code only copies the line if there is any data, if null does not bring back anythin.

What I would Like...

I would like to extend the columns from F:N.
Any if there is no data within F:L and some within L:N, to still copy the row.

Is this possible?

Thanks in advance

Andrew
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
I think this line of code is the issue, but dont know how to amend it so it meets my requirements... Any ideas?



Code:
 For C = 6 To 12 'Cols F:N                              
                         If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12)))
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Well, cross-posting isn't going to do you any favors. The general rule is to at least *mention* that you have posted the same question on (an)other board(s) with a link.

Like one to your post on Ozgrid, here:
http://www.ozgrid.com/forum/showthread.php?t=57211

(For the record, Ozgrid follows that same "rule"--though they tend to enforce them a bit more firmly there. In short, it would be polite to add a link to your post here to the post at Ozgrid).
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
As to the question at hand: You appear to be using COUNTA correctly to count the number of blank cells, however your IF statement itself may be the issue.

Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Then...

If the result of COUNTA is what? The IF statement is looking for an expression that returns true or false. COUNTA simply returns a number. In English, what you've basically ended up with here is "If 3 (result of the counta), then..."

I would specify the result you want to check for, personally:
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) = 0 Then...
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452

ADVERTISEMENT

Von Pookie

Apologies.. I am really desperate to get this to work as I have a dealine to meet...

I tried your suggestion but that just bring back all the data even if there is no data what so ever within the row...

Any other suggestion?
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

What I posted was just an example, really. You do not want to copy if there is no data, correct? Setting the CountA to "if...=0 then" in the context of the macro in your original post will do the opposite--it will copy when there is no data found in the range.

So, you just change it.
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) > 0 Then...

Note the greater than sign in place of the =. If the CountA result is 0, no data was found in the range. If it returns anything greater than 0, there is data.
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
My current code would bring back this row:
AEA - AtkinsonGroveCFB.xls
BCDEFGHIJKLMNOPQRS
15MoniqueSerranoM129255514/08/2006YSICS034
MasterEntry


I would also like to bring back this example to. Even there is no numeric data as seen below, I would still like to bring back the other data, such as date, and sickness codes:
AEA - AtkinsonGroveCFB.xls
ABCDEFGHIJKLMNOPQRS
154MoniqueSerranoM129214/08/2006YSICS034
MasterEntry
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Oh, wow. I appear to have missed a key point of your first post, there:
I would like to extend the columns from F:N.
Any if there is no data within F:L and some within L:N, to still copy the row.
Looks like I probably went from the second post on. Sorry about that. :oops:

Right, ok, then.

Untested, but you could try:
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) > 0 Or Application.CountA(WS.Range(WS.Cells(R, 12), WS.Cells(R, 14))) > 0 Then...
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Thanks for your quick reply Von.

Your code brings back the following: When I only want the 1 row of data.

[HTML removed by admin]
 

Watch MrExcel Video

Forum statistics

Threads
1,114,380
Messages
5,547,599
Members
410,802
Latest member
DataMgmtAnalyst7
Top