Returning a value dependant on a cell...

[Guest]

Hi Everyone,

I have two tables, the first lists letters from AA to ZZ in a lookup type
list and when on of these is selected, for example AB, the corresponding cell
in the second table (the equivalent AB) is populated with a Y.

Table A: Table B:

Type Site Controls Type Has Site Control
AA (various data) AA Y
AB " AB Y
DZ " DZ Y
LW " LW Y

So, as the data is selected in table A, the Y should be populated in table
B. I would appreciate the help on this as it would speed up my tasks
considerably.

Thanks in advance,

Remy

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iPos As Long
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
iPos = Application.Match(Target.Value, Range("M:M"), 0)
If iPos > 0 Then
Cells(iPos, "N").Value = "Y"
End If
End With
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

This assumes the first table is A1:Bn, and the second is M1:Mn. Change to
suit.

 

[Guest]

Bob,

Thanks for the code, I've tried to implement as you said. However, even
after changing the table ranges I got no response from the code, it did
nothing except to give me an error (13) stating a type mismatch...any further
help you could provide?

 

[Bob Phillips]

Try this slightly amended version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iPos As Long
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
On Error Resume Next
iPos = Application.Match(Target.Value, Range("M:M"), 0)
On Error GoTo 0
If iPos > 0 Then
Cells(iPos, "N").Value = "Y"
End If
End With
End If
End Sub