CountIf Function On Column as the Result of the Find.Function

[tlrobinson512]

Okay, so as quickly as I discover the answer to one problem, I create a completely different mountain to climb!

As an extension of my last post, I used the Find.Method to find the Header to the column I was looking for. No problem, I have gotten that down. What I can't figure out, is how to insert a the Application.CountIf function for the column in which I have found. This column will vary from report to report, which is the reason for the Find.Method.

With the CountIf, I need to point the results of that formula back to a cell on my main worksheet. For posting purposes, let's say workbook "Summary" worksheet "Sheet1" Cell A1.

Any thoughts on this? Thanks!

As an example, in the current test sheet I am working with, the column for was column "CF". So I would want to do a =COUNTIF(CF,"BK") and return that value to my main sheet. As stated, please remember that I had to search to find the BK is in column "CF", so I could not pre-define the range for the COUNTIF.

 

[tlrobinson512]

Hi All. Looks like I was able to successfully find the correct format for this question as well! Help on this topic is no longer needed. While my way may add an extra step, I used the following code with the plan to reference the range specified for the remainder of the Sub.

Dim Find1 As String
Dim Rng As Range
Dim Found As Range
Find1 = "EXCPTN_TYPE"
If Trim(Find1) <> "" Then
With Sheets("Reported_Loans_Exceptions").Range("1:1")
Set Rng = .Find(What:=Find1, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If

Set Found = Rng.EntireColumn
Sheets("Reported_Loans_Exceptions").Range("DD2").Value = Application.WorksheetFunction.CountIf(Found, "BK")