Custom formula

[Fish]

Group,

I created a custom function to loop through a specified
text string to look for a combination within a string.
This works fine but on an optional element of the formula
i am getting an error. Even when i take out the optional
part i still get an error. How do i get the function to
return my desired string?

Fish

'''code is as follows

Public Function IDSRCH(Cell_Search As Range, String_Search
As String, Optional Column_refrnc As Range)
Dim IGR As Integer


'''Finds the text Search string
For IGR = 1 To Len(Cell_Search.Value)
Debug.Print Mid(UCase(Cell_Search.Value), IGR, Len
(String_Search))
If Mid(UCase(Cell_Search.Value), IGR, Len
(String_Search)) = UCase(String_Search) Then
'''''''''Trigger now sets to another coumn
If Column_refrnc Is Nothing Then
Cells(Cell_Search.Row, Cell_Search.Column +
2).Value = String_Search
Else
Cells(Cell_Search.Row,
Column_refrnc.Columns.Value) = String_Search
End If
End If
Next IGR

 

[Bernie Deitrick]

Fish,

For starters, try changing

Column_refrnc.Columns.Value

to

Column_refrnc.Column

in the line

Cells(Cell_Search.Row,
Column_refrnc.Columns.Value) = String_Search

But you will need to change from a function to a macro or event, since a
function can only return a value to the object that calls it: it cannot
change the value of another cell.....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

I took at the if statement and declared the
activecell.value = to the string i am looking for but im
still getting an error.

How can i fix so it returns the value

Thanks,

Jeff


Public Function IDSRCH(Cell_Search As Range, String_Search
As String, Optional Column_refrnc As Range)
Dim IGR As Integer


'''Finds the text Search string
For IGR = 1 To Len(Cell_Search.Value)
Debug.Print Mid(UCase(Cell_Search.Value), IGR, Len
(String_Search))
If Mid(UCase(Cell_Search.Value), IGR, Len
(String_Search)) = UCase(String_Search) Then
'''''''''Trigger now sets to another coumn
ActiveCell.Value = String_Search
End If
Next IGR


End Function

 

[Bernie Deitrick]

Jeff,

Since I must have been unclear, let me re-state: you can't do what you are
trying to do. A function cannot change the value of a cell except for the
cell from which it is called. That includes the activecell: the activecell
isn't the cell that is calling the function.

To return a value from a function, you assign the value to the function
name:

IDSRCH = "something"

So if you entered this in cell A1

=IDSRCH(A2:A100, "test")

then in cell A1, you would get "something"

Visit

http://www.cpearson.com/excel/differen.htm

for more information.

HTH,
Bernie
MS Excel MVP