Translate to VBA

[ECUPIRATE]

I created a formula that works perfectly! This formula will count the amount of people on a list by counting the characters in column A, which is number 1- ?. Is there a way to translate this to VBA?

Note:
- the minus 9 and plus 9 represent the count (1-9 that I don't want to divide by.. it's just an offset.
- the minus 10 represents the number of characters in the heading that I don't want to include.

So basically I figured since 1-9 is constant and will always be 1 character, I would remove it... then count all the numbers that have a character of 2 and divide that by 2 to get 1 count... then I added the 9 back which accurately counts the number of people on the chronological list.

=(((SUMPRODUCT(LEN(A:A))-10)-9)/2)+9)))

If anyone could help me translate this formula to VBA that would be awesome!

 

[Veritan]

May I ask why you aren't using the COUNTA formula? Assuming that your header is in cell A1 and you don't want to include that in the count, is there a specific reason you aren't using =COUNTA(A:A)-1 ? As for using it in VBA, maybe something like this?

Sub CountNames()
    Dim x As Integer
    
    x = Application.WorksheetFunction.CountA(Columns("A")) - 1
    Debug.Print x
End Sub

 

[ECUPIRATE]

Thanks, that works well. I'm not using the Counta because Column A has a formula that is Auto-filled down to the Last Row. So, I only want to count the cells that contain 1 or 2 characters.

 

[BobUmlas]

Ans=[(((SUMPRODUCT(LEN(A:A))-10)-9)/2)+9]

 

[ECUPIRATE]

Ans=[(((SUMPRODUCT(LEN(A:A))-10)-9)/2)+9]

I = [SUMPRODUCT(LEN(Sheets("Participants").Range("A:A"))-10)-9)/2)+9] <----- I just tried it, but I'm getting a mismatch error. Any ideas?