Problem with Range.Find and CountA

crugg1 · January 14th, 2009, 05:30 PM

Hello - here is what I'm trying to accomplish and hoping someone can assist:

I want to search the first row (header row) of my data for the cell which contains the text "xyz". I want to then count the number of cells in this column that contain data (CountA). The count result should be returned as an integer for use as a variable in a For/Next Loop.

Thanks much.

stanleydgromjr · January 15th, 2009, 12:56 AM

crugg1,

This should get you started in the right direction:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Code:

 
Option Explicit
Sub Test()
    Dim LR, LC, xyzCol, xyzCountA, Ctr As Long
    Dim ColName As String
    Dim MyRng, MyRng2 As Range
    With ActiveSheet
        LC = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set MyRng = Range(Cells(1, 1), Cells(1, LC))
        xyzCol = Application.WorksheetFunction.Match("xyz", MyRng, 0)
        ColName = Replace(Cells(1, xyzCol).Address(0, 0), 1, "")
        LR = .Cells(Rows.Count, xyzCol).End(xlUp).Row
        Set MyRng2 = .Range(Cells(2, xyzCol), Cells(LR, xyzCol))
        xyzCountA = Application.WorksheetFunction.CountA(MyRng2)
    
        MsgBox "The string 'xyz' in row '1', was found in column '" & ColName & "'," & vbCrLf & vbCrLf & _
            "the last row of data in column '" & ColName & "' is row '" & LR & "'," & vbCrLf & vbCrLf & _
            "and the count of cells with data in this range is '" & xyzCountA & "' cells."
    End With
End Sub

Then run the "Test()" macro.

Have a great day,
Stan

Shasur · January 15th, 2009, 06:31 AM

Quote:

Originally Posted by crugg1

Hello - here is what I'm trying to accomplish and hoping someone can assist:

I want to search the first row (header row) of my data for the cell which contains the text "xyz". I want to then count the number of cells in this column that contain data (CountA). The count result should be returned as an integer for use as a variable in a For/Next Loop.

Thanks much.

Here is a hint

Sub Check_XYX_ROWCnt()
Dim rFnd As Range
Set rFnd = Rows(1).Cells.Find("XYZ")
If Not rFnd Is Nothing Then
MsgBox (WorksheetFunction.CountA(Columns(rFnd.Column)))
End If
End Sub

Cheers
Shasur

crugg1 · January 15th, 2009, 10:34 AM

Both work.

Thank you gentlemen.