Application.CountA issue

[bsnapool]

Hi All

 '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

 

[bsnapool]

I think this line of code is the issue, but dont know how to amend it so it meets my requirements... Any ideas?

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

 

[Von Pookie]

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]

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.

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:

If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) = 0 Then...

 

[bsnapool]

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?

 

[bsnapool]

Is there anybody who can help??? This is driving mr crazy...

Please?

 

[Von Pookie]

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.

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]

My current code would bring back this row:

AEA - AtkinsonGroveCFB.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
15	MoniqueSerrano		M	1292	5	5	5						14/08/2006		Y		SIC	S034
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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
15	4	MoniqueSerrano		M	1292									14/08/2006		Y		SIC	S034
MasterEntry

 

[Von Pookie]

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.

Right, ok, then.

Untested, but you could try:

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]

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]