Case If help needed !!!!

[jamesmev]

I currently am using an "If Target.Address=Range("A2").Address Then_
Range ("J6").Value = 1

The issue is that the Procedure is too long. I have this for over 800 Rows.

How can I write this to shorted but allow an unlimited number of rows without getting the Procedure Too Large Message?

 

[Eric W]

I'd recommend putting the cell values into a list. If it were short enough, you could put the list into arrays or dictionaries. With 800 rows, it would make sense to just make a list in a worksheet. For example:

	A	B
1	A2	J6
2	A3	J7
3	H5	X99
4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

I built this table on Sheet7. Then I created code like this:

Sub testit(target As Range)

    wk = Evaluate("VLOOKUP(""" & target.Address(0, 0) & """,Sheet7!A1:B3,2,FALSE)")
    On Error Resume Next
    Range(wk) = 1

End Sub

It looks up the address in the table, finds the matching cell in the next column, then moves a 1 into that cell. If it is not found in the list, it will return Error 2042, and the On Error line will cause the assignment line to be skipped. There are details to iron out, but this gives the basic gist of it.

 

[jamesmev]

I think the issue with this is the range is always J6.
J6 generates a number which then is indexed to generate a name associated with that number.

So once I click on the Target.Address ("A1") it will generate a number to J6 which is tied to "Sam Samson" which then shows on a different cell...

 

[Eric W]

Is this in a Selection_Change event? The same type of thing can work like this:

	A	B
1	A2	1
2	A3	2
3	H5	3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Sub testit(target As Range)

    wk = Evaluate("VLOOKUP(""" & target.Address(0, 0) & """,Sheet7!A1:B3,2,FALSE)")
    If Not IsError(wk) Then Range("J6") = wk

End Sub