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

 

[Von Pookie]

Sorry about that--had to remove your HTML from that post as it was making the page wonky.

Are the cells truly blank? Even a stray space in the cell(s) would count as data, even if you can't see it. Formulas returning blank results ("") in the cells would count as well.

 

[bsnapool]

No worries..

Yes they will be blank, From F:L are manual input from admin who enter staff sickness and absence (No data = No absence). In M There is a sum calculation of F:L.

M:N is dates of absence. There can be sometimes no data from F:L, but there can still be dates within M:N. I still need to capture this data...

Hope this makes sense?

Is this possible?

Andrew

 

[bsnapool]

I would like the line of code to copy the following::

AEA - AtkinsonGroveCFB.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9	NAME		PaidWorM?	PAYNO	DAYSABSENT							FTEDAYSLOST	Firstdateofabsence	Dateabsenceceased
10					M	T	W	T	F	S	S
11
12	JudithBailey		M	1002
13	HildaConlan		M	651
14	MarkCowell		M	697
15	MoniqueSerrano		M	1292									14/08/2006
MasterEntry

 

[Von Pookie]

Sorry, my coding logic appears to be out of whack today. I believe I know what you're wanting to do.

Very simplistic (and rushed) example:

If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) > 0 Then
    'copy rows
ElseIf Application.CountA(WS.Range(WS.Cells(R, 12), WS.Cells(R, 14))) > 0 Then
    'copy rows
End If

If the first range returns a 0, it will check the second range.

 

[bsnapool]

Thanks for your reply Von...

The code is stall bringing back the dame as before... I have tried taking out the > 0 but still no joy.

Maybe on the second range instead of >0.... Something isdate???

I dont know the syntax for this... forgive my ignorance.

 

[Von Pookie]

If you're using CountA, checking whether or not any of the values are dates does not matter. CountA simply counts the number of cells that have data in them; it does not matter what form that data is in.

That being said, I'm not really sure what to tell you. As far as I can tell what I posted should work. If counting the non-blank cells in F:L returns a 0 (no data) it will count the non-blank cells in L:N.
I'm not quite as rushed at the moment and am going over some of the posts above...perhaps this is part of the issue?

In M There is a sum calculation of F:L.

If you have written the formula in M so that it appears as blank (for example: a cell containing =IF(A1="","",SUM(A1:A2)) would appear as a blank cell), it will be counted as data by the COUNTA function. So even if F:L *and* L:N both have no data, that formula in column M counts.

Would it be possible for you to send me a copy of the workbook (any important/confidential information removed at your discretion)? At this point, I think it may be easier to figure out what is going on and what you want if the actual file is in front of me.

You can send me a PM if you want to send it and I can give you an address to send it to. Of course, if you don't feel comfortable doing so, that's perfectly fine as well

 

[Von Pookie]

(Making a note of the off-board developments.)

Hmm. Perhaps I am right and it *is* the M column throwing things off?
There appears to be no actual formula in that column, just the static
value of '0.00'

So instead of checking for L:N as the second range, you could just
check if N has a value (as L is already counted in the first range
F:L).

Would you still want to copy the row if F:L and N are blank, but M
has a value *other* than 0? Because with the data I'm currently seeing
checking L:N would always return a result due to that 0.00 in M for
each row.

 

[Von Pookie]

WOAH. Hang on a minute...I think I found the problem. (Gaaaah, I'm an idiot )

You have a For/Next loop supposedly checking each column (F:L), but it's not doing anything. You don't need it, since you're already checking by rows. Try this for the loop, instead:

For R = 12 To lRowEnd
    If LCase$(WS.Cells(R, "B").Text) <> "total" Then
           If Application.CountA(WS.Range(WS.Cells(R, "F"), WS.Cells(R, "L"))) Or WS.Cells(R, "N") <> "" 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
            End If
    End If
Next R

This is currently *not* checking column M, though I think it could be easily added if needed.

 

[bsnapool]

Kirsty

You are a legend!! You have cracked it but it is bring validation codes which are listed about 200 rows beneath the data in columns D & E.

I am not clear on what this line of code is doing but this could possibly be the reason??

There is no reason for M to be in there at present, this is just a sum of F:L so if there is no data in here this will be blank...

If LCase$(WS.Cells(R, "B").Text)<> "total" Then

My Summary now looks like this:

ZTeamSummary.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
4	AEA-AtkinsonGroveCFB.xls
5	Week23	MoniqueSerrano		M	1292								0	14/08/2006
6	Week23	KarlSung		M	6709	5	5	5	5	5			0
7	Week23	PaulaGoodwin		W	88657	5	5	5	5	5			0
8	Week23	DannyAshton		W	88279		5	5	5	5	5		0
9	Week23			SUW	Suspendedwithpay
Summary

Kirsty, I really appreciate your help here.

Andrew

 

[Von Pookie]

I am not clear on what this line of code is doing but this could possibly be the reason??

If LCase$(WS.Cells(R, "B").Text) <> "total" Then

Not sure, I didn't write that. Though I can tell you that what that If statement translates to in English:
"If the lowercase version of text in column B is not "total"..."

I haven't really looked at the code other than that problem loop area. I'll take another look at that part, though.