Count/Instr function - How many times "@" appears 4

[VictoriaLJones]

Hi,

I have found something similar to this, but refering to MS Word, and as i have never coded in Word, am not sure how to extrapolate the code and change into an Excel reference etc.

Basically I have worked out the code to loop through each cell in a specific range. However, assuming I am right and need to use the InStr and Count functions, how do I get it to count the number of occurances of "@" (or Char(64)) in each cell, in code, and then give me a total (i guess using some form of counter or i + Count (where Count is the number of occurances etc).

Any suggestions would be great... Apologies if this sounds a bit confused.

Thanks
Victoria

 

[steveouthwaite]

There may be more elegant ways of doing this, but for a starter...

to return the number of occurences of a string within a string, you could use

counter=0
For i = 1 To Len(teststring)
If Mid(test, i, 1) = "@" Then counter = counter + 1
Next i

wrap that inside code to step through your range, and to keep a running total

Good luck
SteveO

 

[woja]

Try:

   Public Function countAts(ByRef aRange As Range) As Long
      Dim iRow As Long
      Dim iCol As Long
      Dim iAt As Long
      Dim iCount As Long
      Dim strVal As String
      iCount = 0
      ' Go through all rows in range
      For iRow = 1 To aRange.Rows.Count
         ' And all columns
         For iCol = 1 To aRange.Columns.Count
            ' Get the cell value
            strVal = aRange.Cells(iRow, iCol).Value
            Do
               ' Look for at in value
               iAt = InStr(strVal, "@")
               If (iAt = 0) Then
                  ' Not found - done
                  Exit Do
               End If
               iCount = iCount + 1
               ' remove string before & including @
               strVal = Mid(strVal, iAt + 1)
            Loop
         Next iCol
      Next iRow
      countAts = iCount
      Exit Function

Hope this helps

[tt]_______________________________________
Roger
The Eileens are out there[/tt]

 

[woja]

whoops..
missed:

   End Function

off the end...

[tt]_______________________________________
Roger
The Eileens are out there[/tt]

 

[SkipVought]

Victoria,

Make you a function to return the count...

Function NumOfAt(rng as Range)
  NumOfAt = 0
  for i = 1 to len(rng.value)
    if mid(rng.value, i, 1) = "@" then _
      NumOfAt = NumOfAt + 1
  next
End Function

pass the range. I use range so you can use on a sheet as well.




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

Hi Victoria
You have the right idea. This will do the trick. Use of InStr isn't essential but I have used it here to test whether @ exists in the string and then to start the loop from where the first occurance is.

Sub lime()
Dim iCount As Integer
Dim i As Integer
With ActiveCell 'or whatever ref to cell to check
    If Not InStr(1, .Text, "@") = 0 Then
        For i = InStr(1, .Text, "@") To Len(.Text)
            If Mid(.Text, i, 1) = "@" Then
                iCount = iCount + 1
            End If
        Next i
    End If
End With
MsgBox iCount
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[VictoriaLJones]

Thank-you!!

Stars for you all...... )