Create a function in Excel VBA to test if a cell contains a certain character

Question

I'm looking to create a function in Excel/VBA that will look at a range of cells and return a value of TRUE if any of the cells contains a certain character (an asterisk *). There will be some blank cells, some cells will contain text and some may contain text and an asterisk. Can anyone help? Many thanks

Answer 1

Copy and paste the below code into a new module

Function ContainsStar(r As Range) as Boolean
    Dim i As Long
    Dim cell As Range
    Dim contains As Boolean
    For Each cell In r.Cells
        For i = 1 To Len(cell)
            If Right(Left(cell, i), 1) = Chr(42) Then
                contains = True
                GoTo ExitFn
            End If
        Next i
    Next
    Exit Function
ExitFn:
    ContainsStar = contains
    Exit Function
End Function

then use it in the spreadsheet like this

Note:

D1 = =ConstainsStar(A1:A3)

H1 = =ConstainsStar(E1:E3)

Answer 2

you can also use the instr function

function containstar(byval r as range) as boolean
dim cel as range 'be careful about variable names that are already existing code (cell)
for each cel in r.cells   
'or use a loop like: for i=1 to r.cells.count , and use r.cells(i), if you prefer.
  if instr(1,cel.value,"*")>0 then
     containstar=true
     exit sub   'you can also exit for
  end if
next cel
containstar=false
end sub

and to call the function:

a=containstar("a1:b8")

use only goto when you cannot do else, usually you don't need it.