Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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 "Fish" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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 -----Original Message----- 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 "Fish" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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 wrote in message ... 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 -----Original Message----- 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 "Fish" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom field with custom formula in pivot table! | Excel Worksheet Functions | |||
custom add formula | Excel Worksheet Functions | |||
Custom cell formula | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming | |||
Custom formula | Excel Programming |