User-Defined Functions

css navigation by Css3Menu.com

Reduce Typing and Tear

I saw my buddy on the next aisle struggling with this mile long formula that breaks every time it is copied down.

Wouldn’t it be neat if he just had to choose a couple parameters and it would compute automatically.

Function PutTake(LowDate, HiDate As Date, TheAnswer)
    Dim CurCell     As Long
    Dim AnsCol      As Long
    Dim DateColLow  As Long
    On Error GoTo ErrHandles
    '=IF('Detail (Data Entry)'!$R700=" ",0,IF(AND('Detail (Data Entry)'!R700>=D1, 'Detail (Data Entry)'!R700<=E1),'Detail (Data Entry)'!AG700,0))
    CurCell = ActiveCell.Row    'Get Current Row
    AnsCol = TheAnswer.Column   'Get Column # of Answer
    DateColLow = LowDate.Column 'Get Column # of Low Date
    If Sheets("Detail (Data Entry)").Cells(CurCell, DateColLow) = "" Then
        PutTake = 0             ‘if is blank
    Else
        If Sheets("Detail (Data Entry)").Cells(CurCell, DateColLow) >= LowDate Then
            If Sheets("Detail (Data Entry)").Cells(CurCell, DateColLow + 1) <= HiDate Then
                PutTake = Sheets("Detail (Data Entry)").Cells(CurCell, AnsCol) 'address Row,Col
            End If
        Else
            PutTake = 0     'If dates not satisfied, then zero
        End If
    End If
    Exit Function
ErrHandles:
    PutTake = "Error"       'If bombs,
End Function

Orignally, my variable were named things like “daAnswer” and “daNumber” but the guys laughed at my hillbilly names.

Remember, this is just an example; your results may vary.


© 2013-2024

Updated:  06/21/2024 07:42
This page added:  06 August 2013