Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Efficient alternative to using Target.Address

Tags:

I'm currently using Target.Address to identify when a cell is double clicked and run a piece of code to take the user to a relevant sheet showing the information contained in that cell.

So for example, if a cell says that 3 systems haven't done some sort of action, if a user clicks on that cell they get taken to what those 3 systems are.

Currently, I'm doing this like so:

If Target.Address = "$B$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(9))
ElseIf Target.Address = "$C$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(0))
ElseIf Target.Address = "$D$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(1))
etc

I've put the majority of the code in one small function, so this seems to be doing the job okay. However, if I were to insert a new row above row 20 (from the above example), all of these references would be pointing to the wrong place. I thought I could handle this by removing the absolute references (the $ sign) but that just breaks the mechanism.

Can anybody advise how I could either a) rewrite the code to make it more efficient and b) protect the code so new rows can be inserted and the code will remember which rows/columns it was pointing to and update itself accordingly.