0

Pretty simple function, take the data in the expected range, do some stuff, then do some other stuff and assign that other stuff to ActiveCell.Offset(0,1).Value

Nope. Error 1004.

But wait. You want to know what's in that cell? Oh that's the random data you put in there. Change the data? Uh....nope nope nope nope nope...

Function fixData(rng As Range) As String
    Dim feet As Integer, inches As Integer
    'On Error Resume Next
    feet = DatePart("m", rng.Value)
    inches = inches + (feet * 12)

    Debug.Print ActiveCell.Offset(0, 1).Value & ", Inches = " & inches
    ActiveCell.Offset(0, 1).Value = inches

    'Debug.Print DatePart("m", rng.Value) & "'" & DatePart("d", rng.Value) & """"
    fixData = DatePart("m", rng.Value) & "'" & DatePart("d", rng.Value) & """"

    If Err Then
        Debug.Print "error " & Err.Number
    End If
End Function

Maybe I'm just code blind from sitting in front of the computer too long... Help please.

Community
  • 1
  • 1
Ken Ingram
  • 1,538
  • 5
  • 27
  • 52
  • How are you invoking the function? If it is used as a UDF and called from a cell formula then only that cell can be modified by it. – Cool Blue Dec 28 '14 at 03:03
  • It is a Udf called as =PERSONAL.XLSB!fixData() So Formulas cannot alter any cells other than the calling cell? I have to use Sub instead? – Ken Ingram Dec 28 '14 at 03:07
  • There are work-arounds, involving Application.OnTime for example, but you would be much better off invoking the Function (or Sub) from an event handler like [Worksheet.Change](http://msdn.microsoft.com/en-us/library/ff841127(v=office.15).aspx) combined with the [Application.Intersect Method](http://msdn.microsoft.com/en-us/library/ff198091(v=office.15).aspx). – Cool Blue Dec 28 '14 at 03:20

0 Answers0