Nothing specific springs to mind, just remember seeing that bit of code before and the advice was not to use it. It works for what I need but I'd be interested to see if my code is any use or not. And in the interest of learning:
Function DC(constant_required, year)
constant_required = UCase(constant_required) ' just to trap for failure. DO same for year
'column_x = 2
While ThisWorkbook.Worksheets("sheet2").Cells(2, column_x) <> year
column_x = column_x + 1
If column_x = 200 Then
answer = "YEAR?"
GoTo early_end ' just a max limit
End If
Wend
row_x = 3
While ThisWorkbook.Worksheets("sheet2").Cells(row_x, 1) <> constant_required
row_x = row_x + 1
If row_x = 400 Then ' to trap for too long if non-existent
answer = "VARIABLE?"
GoTo early_end
End If
Wend
answer = ThisWorkbook.Worksheets("sheet2").Cells(row_x, column_x)
early_end:
'to trap to stop it running forever
DC = answer
End Function
As background DC takes a variable (which in this situation would be the month - but ti has applications beyond that) and a year. The table is on sheet2 and is a simple case of years along the top months down the side. It was the simplest way I could think of doing it to be honest. I'm no coder, just someone who has dabbled with VBA / Excel for far too long!
Bookmarks