Custom function help (1 Viewer)

[laxster]

Function previous(ByVal status_x As Range, ByVal date_x As Range, ByVal bin_x As Range) As String
    For Each C In Sheet6.Range(Range("F9"), Range("F9").End(xlDown)).Cells
 
        If Month(C.Value) = Month(date_x.Value) And Day(C.Value) < Day(date_x.Value) And Cells(C.Row, bin_x.Column).Text = bin_x.Text Then
               If Cells(C.Row, status_x.Column).Text = "NFI" Or Cells(C.Row, status_x.Column).Text = "Hang" Or Cells(C.Row, status_x.Column).Text = "Empty" Then
                    Update = Cells(C.Row, status_x.Column).Value
                    Exit Function
               End If
        End If
 
    Next
 
    previous = status_x.Value
 
 
End Function

I've created this custom function to look up a previous status of bins entered earlier in the sheet if the current conditions are "Empty", "Hang", or "NFI".

It seems to work fine with hangs and empties, but with NFIs it spits back #NAME? errors. Not sure why.

Any idea - am I messing up on the syntax somewhere?

 

[Brianwarnock]

Since there is no syntax difference between those that you say work and the one that doesn't I think that to help we would need to see the spreadsheet and run it ourselves.

Brian

 

[laxster]

I've attached a "stripped down" version of the file with the relevant tabs. The tab that has the information driving this is the Entry tab, and is used in column T.

I hope you're able to catch what I'm missing or provide some insight!

 

[Brianwarnock]

Sorry I don't have 2007 and the file conversion leaves me with errors especially as I don't therefore have the Update worksheet function. I presume this is new in 2007.

However I also could not find where you are using your function.

Brian

 

[Brianwarnock]

I think that you must have renamed your function change all references to previous to update


IE

Function update(ByVal status_x As Range, ByVal date_x As Range, ByVal bin_x As Range) As String

    For Each C In Sheet6.Range(Range("F9"), Range("F9").End(xlDown)).Cells
    
        If Month(C.Value) = Month(date_x.Value) And Day(C.Value) < Day(date_x.Value) And Cells(C.Row, bin_x.Column).Text = bin_x.Text Then
               If Cells(C.Row, status_x.Column).Text = "NFI" Or Cells(C.Row, status_x.Column).Text = "Hang" Or Cells(C.Row, status_x.Column).Text = "Empty" Then
                    update = Cells(C.Row, status_x.Column).Value
                    Exit Function
               End If
        End If
        
    Next
    
    update = status_x.Value

End Function

Brian

 

[laxster]

I am trying to make one more tweak to this bit of code, to no avail.

Basically, I want "Hangs" to continue to show indefinitely -- ignoring the month they occur in. This is different than "Empty" bins, which simply stop the display of Empty after the month is up.

Been fiddling with the code above to get this to occur, but having issues. Am I missing something simple?

 

[Brianwarnock]

It should be simple enough to take this
Cells(C.Row, status_x.Column).Text = "Hang"
into a separate If statement but I don't have time now, I'll try over the weekend.

Brian

 

[Brianwarnock]

I haven't looked at it yet but can you not just change the If
If(T9="hangs","hangs",If etc

I seem to remember t9 being the first cell with the formula in but anyway you get the idea.

Brian