Is it possible to change a value of another cell with a UDF?

[cvraman]

Hi guys,
Im kind of beginner in vba.
Below is a simple udf.
If two criteria match it will return with the value from the WorksheetFunction.Index(Sheets("TP_IP_range").Range("C:C"), add, 0).

I would like to change the value of Worksheets("TP_IP_range").Range("B" & add"). Is it possible somehow??


Function tp_ip()


Dim add As Integer
Dim start As Integer
Dim rng As Range
Dim ip As String


start = 0
Set rng = Workbooks("Ran_ports_master.xlsm").Worksheets("TP_IP_range").Range("E:E")


Do
add = WorksheetFunction.Match("-", rng, 0)
add = add + start
ip = WorksheetFunction.Index(Sheets("TP_IP_range").Range("C:C"), add, 0)

If WorksheetFunction.Index(Sheets("TP_IP_range").Range("A:A"), add, 0) <> "" Then
start = add
add = add + 1
Set rng = Worksheets("TP_IP_range").Range("E" & add & ":E17000")
Else
tp_ip = ip
Exit Do
End If


Loop
End Function


thanks in advance

 

[dUBBINS]

Worksheets("TP_IP_range").Range("B" & add").Value=

Then just add what ever you want the cell to be populated with after the equal sign. Could be a value, variable, formula, etc.

 

[cvraman]

Hi dUBBINS,

Thank you for your help but unfortnately it is not working.
Worksheets("TP_IP_range").Range("B" & add).Value= "ok"

I think it is not possible to solve with "basic" solutions. How it looks it is not possible to change the environment in excel with the use of a function: http://support.microsoft.com/kb/170787
I was just hoping that there is a loophole for it :D

thanks