Returning a value dependant on a cell...

[RemySS]

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.

--
HTH

Bob Phillips

"RemySS" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[RemySS]

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" wrote:

> 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.
>
> --
> HTH
>
> Bob Phillips
>
> "RemySS" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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


--
HTH

Bob Phillips

"RemySS" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "RemySS" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >