software

I have a worksheet (Data):

A B C
IMS Line
Accurate y
Banctec y y
Advisor y
Excel y

There are 12 rows. Column A is a list of services, IMS and LIne are
components used by those services. In this example Accurate uses IMS so
there is a y in cell B2.

I have a form with 2 listboxes, one (lbcomp) is autopopulated using
cells B1:C1. the other (lbsource) is autopopulated using cells A2:A12.
When a user selects a component from lbcomp I want all of the services
with a y against them to be highlighted, so if someone chooses IMS I
want Accurate, Banctec and Excel to be selected in lbsource.

So far I have written code which makes a msgbox report whethere the
service is used or not (i haven't quite figured out the selection part
yet). My code works for the first two rows but when it hits the third
time it fails and I get a Runtime error 13, type mismatch error. I am
new to VBA and tend to clone what I can from this group and others so
this has me stumped. The offending code follows:

Private Sub Lbcomp_Click()
Dim iloop As Integer
Dim res As String

For iloop = 0 To lbsource.ListCount - 1res = Application.Index(Worksheets(quot;Dataquot;).Range(quot;b2:c12 quot;), _
Application.Match(lbcomp.Text, _
Worksheets(quot;Dataquot;).Range(quot;b1:c1quot;), 0), _
Application.Match(lbsource.List(iloop), _
Worksheets(quot;Dataquot;).Range(quot;a2:a12quot;), 0))

If res = quot;yquot; Then
MsgBox lbsource.List(iloop) amp; quot; uses this component.quot;

Else:
If res = quot;quot; Then
MsgBox lbsource.List(iloop) amp; quot; doesn 't use this component.quot;

Else

If IsEmpty(res) Then
MsgBox quot;res is empty!quot;
End If
End If

End If

Next iloop
End SubAny help would be greatly appreciated!
Thanks very muchSharon,

You have reversed the row and column indices in your index function:

gt; res = Application.Index(Worksheets(quot;Dataquot;).Range(quot;b2:c12 quot;), _
gt; Application.Match(lbcomp.Text, _
gt; Worksheets(quot;Dataquot;).Range(quot;b1:c1quot;), 0), _
gt; Application.Match(lbsource.List(iloop), _
gt; Worksheets(quot;Dataquot;).Range(quot;a2:a12quot;), 0))

should be

res = Application.Index(Worksheets(quot;Dataquot;).Range(quot;b2:c12 quot;), _
Application.Match(lbsource.List(iloop), _
Worksheets(quot;Dataquot;).Range(quot;a2:a12quot;), 0), _
Application.Match(lbcomp.Text, _
Worksheets(quot;Dataquot;).Range(quot;b1:c1quot;), 0))

As for selecting (highlighting) the applicable items in lbsource, use code like

If LCase(res) = quot;yquot; Then
lbsource.Selected(iloop) = True
End If

Make sure you set the MultiSelect property of lbsource to

1 - fmMultiSelectMulti

and get rid of all your message boxes.

HTH,
Bernie
MS Excel MVPquot;Shazbotquot; gt; wrote in message oups.com...
gt;I have a worksheet (Data):
gt;
gt; A B C
gt; IMS Line
gt; Accurate y
gt; Banctec y y
gt; Advisor y
gt; Excel y
gt;
gt; There are 12 rows. Column A is a list of services, IMS and LIne are
gt; components used by those services. In this example Accurate uses IMS so
gt; there is a y in cell B2.
gt;
gt; I have a form with 2 listboxes, one (lbcomp) is autopopulated using
gt; cells B1:C1. the other (lbsource) is autopopulated using cells A2:A12.
gt; When a user selects a component from lbcomp I want all of the services
gt; with a y against them to be highlighted, so if someone chooses IMS I
gt; want Accurate, Banctec and Excel to be selected in lbsource.
gt;
gt; So far I have written code which makes a msgbox report whethere the
gt; service is used or not (i haven't quite figured out the selection part
gt; yet). My code works for the first two rows but when it hits the third
gt; time it fails and I get a Runtime error 13, type mismatch error. I am
gt; new to VBA and tend to clone what I can from this group and others so
gt; this has me stumped. The offending code follows:
gt;
gt; Private Sub Lbcomp_Click()
gt; Dim iloop As Integer
gt; Dim res As String
gt;
gt; For iloop = 0 To lbsource.ListCount - 1
gt;
gt;
gt; res = Application.Index(Worksheets(quot;Dataquot;).Range(quot;b2:c12 quot;), _
gt; Application.Match(lbcomp.Text, _
gt; Worksheets(quot;Dataquot;).Range(quot;b1:c1quot;), 0), _
gt; Application.Match(lbsource.List(iloop), _
gt; Worksheets(quot;Dataquot;).Range(quot;a2:a12quot;), 0))
gt;
gt; If res = quot;yquot; Then
gt; MsgBox lbsource.List(iloop) amp; quot; uses this component.quot;
gt;
gt; Else:
gt; If res = quot;quot; Then
gt; MsgBox lbsource.List(iloop) amp; quot; doesn 't use this component.quot;
gt;
gt; Else
gt;
gt; If IsEmpty(res) Then
gt; MsgBox quot;res is empty!quot;
gt; End If
gt; End If
gt;
gt; End If
gt;
gt; Next iloop
gt; End Sub
gt;
gt;
gt; Any help would be greatly appreciated!
gt; Thanks very much
gt;
This worked ok for me (as a userform, right????):

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()

Dim iCtr As Long
Dim WhichColumn As Long

If Me.ListBox1.ListIndex = -1 Then
'nothing selected
Else
WhichColumn = Me.ListBox1.ListIndex 1
With Me.ListBox2
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = CBool(LCase(.List(iCtr, WhichColumn)) = quot;yquot;)
Next iCtr
End With
End If

End Sub
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets(quot;Dataquot;)

Set myRng = wks.Range(quot;B1:C1quot;)
With Me.ListBox1
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
.MultiSelect = fmMultiSelectSingle
End With

Set myRng = wks.Range(quot;a2:C12quot;)
With Me.ListBox2
.ColumnCount = 3
.ColumnWidths = quot;12;0;0quot;
.List = myRng.Value
.MultiSelect = fmMultiSelectMulti
End With
End SubShazbot wrote:
gt;
gt; I have a worksheet (Data):
gt;
gt; A B C
gt; IMS Line
gt; Accurate y
gt; Banctec y y
gt; Advisor y
gt; Excel y
gt;
gt; There are 12 rows. Column A is a list of services, IMS and LIne are
gt; components used by those services. In this example Accurate uses IMS so
gt; there is a y in cell B2.
gt;
gt; I have a form with 2 listboxes, one (lbcomp) is autopopulated using
gt; cells B1:C1. the other (lbsource) is autopopulated using cells A2:A12.
gt; When a user selects a component from lbcomp I want all of the services
gt; with a y against them to be highlighted, so if someone chooses IMS I
gt; want Accurate, Banctec and Excel to be selected in lbsource.
gt;
gt; So far I have written code which makes a msgbox report whethere the
gt; service is used or not (i haven't quite figured out the selection part
gt; yet). My code works for the first two rows but when it hits the third
gt; time it fails and I get a Runtime error 13, type mismatch error. I am
gt; new to VBA and tend to clone what I can from this group and others so
gt; this has me stumped. The offending code follows:
gt;
gt; Private Sub Lbcomp_Click()
gt; Dim iloop As Integer
gt; Dim res As String
gt;
gt; For iloop = 0 To lbsource.ListCount - 1
gt;
gt; res = Application.Index(Worksheets(quot;Dataquot;).Range(quot;b2:c12 quot;), _
gt; Application.Match(lbcomp.Text, _
gt; Worksheets(quot;Dataquot;).Range(quot;b1:c1quot;), 0), _
gt; Application.Match(lbsource.List(iloop), _
gt; Worksheets(quot;Dataquot;).Range(quot;a2:a12quot;), 0))
gt;
gt; If res = quot;yquot; Then
gt; MsgBox lbsource.List(iloop) amp; quot; uses this component.quot;
gt;
gt; Else:
gt; If res = quot;quot; Then
gt; MsgBox lbsource.List(iloop) amp; quot; doesn 't use this component.quot;
gt;
gt; Else
gt;
gt; If IsEmpty(res) Then
gt; MsgBox quot;res is empty!quot;
gt; End If
gt; End If
gt;
gt; End If
gt;
gt; Next iloop
gt; End Sub
gt;
gt; Any help would be greatly appreciated!
gt; Thanks very much

--

Dave Peterson

Thank you some much both of you, I tried yours first Bernie (it was
first and also less effort for me!). It worked perfectly. I have no
doubt yours would have worked too Dave, I am in these groups often
enough to know what kind of success rate you have! Thanks again, you
have saved me another day of scratching my head! I can't believe I had
mixed up the rows/columns....no wonder it failed on the third attempt!
It seems so simple now you have pointed it out but I doubt I would have
ever twigged.