Dim IndxNum As Long & Dim c As Variant

  • Thread starter Thread starter Howard
  • Start date Start date
H

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?

I have Multi Line = TRUE in properties.

Thanks,
Howard

Option Explicit

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

Set IndexCol = Range("A2:A7")

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

For Each c In Range("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
TextBox1.Text = MyStringVariable
End If
Next ' c

End Sub
 
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?
Click to expand...

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.
 
Hi Howard,

Am Sat, 13 Jul 2013 22:32:18 +0200 schrieb Claus Busch:
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
Click to expand...

you don't need .value because value is the standard property of a range.
So the code is better readable. And your 6 cell values are too long for
a textbox. Make a vbCr after the 4. entry (you also forgot one comma):

MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _
& vbCr & c.Offset(0, 15) & ", " & c.Offset(0, 18)


Regards
Claus B.
 
Hi Howard,



Am Sat, 13 Jul 2013 22:32:18 +0200 schrieb Claus Busch:










you don't need .value because value is the standard property of a range.

So the code is better readable. And your 6 cell values are too long for

a textbox. Make a vbCr after the 4. entry (you also forgot one comma):



MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _

& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _

& vbCr & c.Offset(0, 15) & ", " & c.Offset(0, 18)





Regards

Claus B.
Click to expand...

For sure I had not got far enough to see that the comma was missing, did find that while testing your very workable adjustments to the code.

Two things Claus.

I knew before any running any successful code I was going to have anywhere from 30 to maybe 60 characters returned so I just made the text box about 8normal columns wide and that is wider than I need for the 50 or so characters that I'm returning on a single line. I just figured I'd narrow it up later to suit the entries.

A single line for each entry in the text box is a goal which doesn't seem to be a problem. But you would not have said anything if you did not have some concern about the width of the text box. So I'm open to suggestion about this.

However, if I try to add additional strings to the text box, it overwrites the previous entry. Can't seem to figure out how to add subsequent stringsbelow the last . Which is also a goal.

Multiple strings on a single line is what I am shooting for.

Howard
 
Hi Howard,

Am Sat, 13 Jul 2013 14:45:14 -0700 (PDT) schrieb Howard:
A single line for each entry in the text box is a goal which doesn't seem to be a problem. But you would not have said anything if you did not have some concern about the width of the text box. So I'm open to suggestion about this.
Click to expand...

you can set the width as you like it and you can work with the textbox.
It is only a optical thing
However, if I try to add additional strings to the text box, it overwrites the previous entry. Can't seem to figure out how to add subsequent strings below the last . Which is also a goal.
Click to expand...

you can hold your former entries in the textbox if you change the code
to:
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable

And that you must not loop through your range you could use Range.Find:

Set c = IndexCol.Find(IndxNum, LookIn:=xlValues)
If Not c Is Nothing Then
MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & vbCr & c.Offset(0, 14) _
& ", " & c.Offset(0, 15) & ", " & c.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If

Make sure that in this case c has to be declared as range


Regards
Claus B.
 
Hi Howard,

Am Sat, 13 Jul 2013 23:58:37 +0200 schrieb Claus Busch:
you can set the width as you like it and you can work with the textbox.
It is only a optical thing
Click to expand...

you also can set the property "AutoSize" to true and the textbox will
adjust to the text.


Regards
Claus B.
 
Hi Howard,



Am Sat, 13 Jul 2013 23:58:37 +0200 schrieb Claus Busch:







you also can set the property "AutoSize" to true and the textbox will

adjust to the text.
Click to expand...


Well, Claus, once again you got me where I wanted to go. Everything works just fine!

I do appreciate the help.

Thanks.
Howard
 
Claus said:
you don't need .value because value is the standard property of a range.
Click to expand...

correct. But I also write .value explicitely when I mean it.
In my opinin, this makes the code better readable than leaving it out.

Volker
 
Back
Top