Hi Macroman,

There is no way you can change anything in your spreadsheet (values,
formats, etc) from within a function.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Macroman" wrote in message
...
I am writing a simple function which asks the user for a range of cells.

I would like the range of cells analysed and replaced with new values but
I am unable to write back to the spreadsheet.
I can create a Sub (macro) to do this but I prefer a function then I can
add the function to my UDF AddIns.
The code as follows
-----------
Function Grab(Grab_Range As Range)

intCellLoc = ActiveCell.Address
varSheetName = ActiveSheet.Name
intRows = Grab_Range.Rows.Count
intColumns = Grab_Range.Columns.Count

intRepRow = Grab_Range.Cells(1, 1).Row
intRepCol = Grab_Range.Cells(1, 1).Column

If intColumns < 1 Then ErrMsg = "#Column Err#": GoTo Finished

########It is this bit I cant get working#############
For i = 1 To intRows
Sheets(varSheetName).Cells(intRepRow+(i-1) , intRepCol).Value = "Test"
Next
######## Any Ideas ? ############
Finished:
If ErrMsg = "" Then ErrMsg = "Finished"
Grab = ErrMsg
End Function

--------------
thanks

Macroman