Hi Howard,

Am Sat, 13 Jul 2013 13:10:27 -0700 (PDT) schrieb Howard:

I believe the problem is with the InputBox IndxNum and the Variant c.

The IndxNum in this case is a number and the For Each c requires a Variant.
Seems they not be compatible.

Also, have not got far enough to test it, but will the next MyStringVariable be listed below the previous or do I need the & vbCr at the end of MyStringVariable?

c has to be a range. You don't need a vbCr at the end of the string.
Try:

Sub TheT_Box()
Dim MyStringVariable As String
Dim IndxNum As Long
Dim IndexCol As Range
Dim c As Range

Set IndexCol = Range("A2:A7")

IndxNum = Application.InputBox(Prompt:="Enter an Number.", _
Title:="Enter Index Number", Type:=1) ' 1 = number
If IndxNum = False Then Exit Sub

For Each c In IndexCol 'Range("A2:A7")
If c.Value = IndxNum Then
MyStringVariable = c.Offset(0, 4).Value & ", " & c.Offset(0,
1).Value _
& c.Offset(0, 2).Value & ", " & c.Offset(0, 14).Value _
& ", " & c.Offset(0, 15).Value & ", " & c.Offset(0, 18).Value '&
vbCr
ActiveSheet.TextBox1.Text = MyStringVariable
End If
Next ' c

End Sub

You can test IndxNum also against greater MAX(IndexCol) and smaller
MIN(IndexCol)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2