Capture Count of Records from a Cell Value or through Count of Rows (using If Condition)

[ragav_in]

Dear All,

Currently I am working on multiple workbooks where I need to extract some information from the last cell in Column A. Each files are of different format. The format of Column A is that it has data, and the bottom of the column it contains a text "Count of Records: 1234". Basically this tells how many records are there in that excel workbook. I wrote a code to extract the value of '1234' from this cell to copy into another summary sheet

Dim rng as Range, reccount as Long
Set rng = ActiveSheet.Columns("A:A")
reccount = Split(rng.Find(What:="Record Count", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False), ":")(1)

The above code works if the text "Record Count" is available in Column A, but in some cases due to human interference, the cell that contains the summary of records is deleted. What I am trying to do is to find how many rows of data are available in this case.

The requirement is that the Code should look for the "String" "Record Count:" and extract the number from the cell; if it does not find the "String" in Column A, it has to to count the number of rows in that file and provide that as the "reccount". May I request this forum to provide me some support and guidance on how to write the code for this If Condition

If String "Record Count" exists, it should extract the number from the cell; if the excel file does not have the String "Record Count", then it has to count the Number of Rows in that file.

Note: The reason on why I use the code of "rng.Find (What:=)" is that the cell with the value "Record Count" can lie anywhere in the Column A (non-contiguous range) and hence End(xlDown) will not work (see attached screenshot).

I am thankful to anyone who puts their time and effort to read this post and help me with suggestion.

Thanks,
ragav_in

 

[Anthony47]

Should your count include empty cells, or only those with values in them?
Could we forget about this phantom "Record Count: xxx" line and simply count the lines to get the result?

 

[ragav_in]

Should your count include empty cells, or only those with values in them?
Could we forget about this phantom "Record Count: xxx" line and simply count the lines to get the result?

Dear Anthony, I need to count the number of rows of data just before the cell that has the value "Record Count". The reason why I have been stressing on the "Record Count" cell is that

1. The cell with value Record Count can be anywhere in Column A and need not necessarily be immediately after the dataset ends (in the below example, you see it after 2 cells after the dataset ends)
2. In some reports, the cell with Record Count Value exists and in some reports, it does not (the cell gets deleted by mistake)
3. I am unable to use the .UsedRange option as it does not provide the correct count as the template is reused from other documents and hence the last used cell varies with the number of rows of data in the workbook
4. I already found a way to extract the value if the workbook contains the Record Count cell (in my original post), I am looking for an If condition to count the number of rows if the Record Count cell is not available in the workbook
5. The template of the report is provided below. It always contains 2 Rows of Header by default, then the dataset, and then the cell with the value of Record Count may or may not exist

I have attached the sample format of my excel file for you to visualize. From the below sheet, we need to get "10" as the reccount if the cell exists with the value or if not, the macro has to count the number of rows between 3 to 12 and provide us the result of 10. Please let me know if you need more information.

Sample-Report.xlsx
	A	B	C	D	E
1	Project	Division	Source	Model	Target
2	Project	Division	Source	Model	Target
3	AB_CD_123	People	FlatFile	Objectg 1	email.address@email.com
4	AB_CD_123	People	FlatFile	Objectg 2	email.address@email.com
5	AB_CD_123	People	FlatFile	Objectg 3	email.address@email.com
6	AB_CD_123	People	FlatFile	Objectg 4	email.address@email.com
7	AB_CD_123	People	FlatFile	Objectg 5	email.address@email.com
8	AB_CD_123	People	FlatFile	Objectg 6	email.address@email.com
9	AB_CD_123	People	FlatFile	Objectg 7	email.address@email.com
10	AB_CD_123	People	FlatFile	Objectg 8	email.address@email.com
11	AB_CD_123	People	FlatFile	Objectg 9	email.address@email.com
12	AB_CD_123	People	FlatFile	Objectg 10	email.address@email.com
13
14
15	Record Count: 10
MyReport

Thanks in advance for your time and support in this regard.
Thanks, ragav_in

 

[Anthony47]

My suggestion is that you forget about the embedded counter and count how many records are in column A:

Dim tArea As Range, GhostR As Long, RecCnt As Long

Set tArea = Sheets("Foglio1").Range("A:A")
GhostR = Application.WorksheetFunction.CountIf(tArea, "Record Count:*")
RecCnt = Application.WorksheetFunction.CountA(tArea) - GhostR - 2

 

[ragav_in]

My suggestion is that you forget about the embedded counter and count how many records are in column A:

Dim tArea As Range, GhostR As Long, RecCnt As Long

Set tArea = Sheets("Foglio1").Range("A:A")
GhostR = Application.WorksheetFunction.CountIf(tArea, "Record Count:*")
RecCnt = Application.WorksheetFunction.CountA(tArea) - GhostR - 2

Hi Anthony47,
Thanks for the suggestion. However, please note that the above method does not work in all scenarios where the Record Count cell exists or not. The 'tArea' returns different value when the cell with Record Count exists in Column A and returns 1 count lesser when the cell Record Count does not exist in Column A. This is due to the reason that the used range in Column A is not the actual number of Columns. This was the reason on why I used the below line of code to extract the Record Count from Column A as the location of cell with Record Count value is not consistent across multiple documents

rng.Find(What:="Record Count", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

I know my requirement is getting a bit hazy and confusing and hence I will try to express my requirement here. What I need is an If Condition that looks for Column A for the String "Record Count" and extracts the number from it as the 'reccount'. If it does not find the String "Record Count" in Column A, it has to count the number of records from Column C as 'reccount'. I am not sure of how this If condition will be, but I am trying to provide a pseudo If condition below to try and express my requirement

Dim sArea, tArea As Range
Set sArea = ActiveSheet.Columns("A:A")
Set tArea = ActiveSheet.Columns("C:C")
[B]If Column A contains the String "Record Count" Then [/B]
    reccount = Split(sArea.Find(What:="Record Count", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False), ":")(1)
ElseIf: reccount = Application.WorksheetFunction.CountA(tArea) - 2
EndIf

It would be of great help if anyone can help me with the If Condition as highlighted in Bold text can be implemented, I will have my solution.

Once again, thanks everyone for your support and help in this regard.
ragav_in

 

[Anthony47]

Thus

Dim tArea As Range, sArea As Range, RecCount As Long

Set tArea = ActiveSheet.Range("C:C")
Set sArea = ActiveSheet.Columns("A:A")
'
GhostR = Application.WorksheetFunction.CountIf(sArea, "Record Count:*")
If GhostR > 0 Then
    RecCount = Split(sArea.Find(What:="Record Count", LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False), ":")(1)
Else
    RecCnt = Application.WorksheetFunction.CountA(tArea) - 2
End If

(but I am still convinced that using ContIf & CountA should be sufficient)