What is the proper Type if the input data in an input box is as follows, is this a string or an array? The individual characters will later be used as numbers in a If c.value = OneOfTheseNumbers Then

Entries could be:

1,9
1,2,3,11,6,13
6

Type:=2 Text (a string)
Type:=64 An array of values

And how can I take this "set" of entries from the InputBox and list them in a column as individual numbers. (That range will be Dim IndexLibary As Range("XX:YY")in the code)

Thanks,
Howard

Hi Howard,

Am Sun, 14 Jul 2013 08:44:14 -0700 (PDT) schrieb Howard:

What is the proper Type if the input data in an input box is as follows, is this a string or an array? The individual characters will later be used as numbers in a If c.value = OneOfTheseNumbers Then

Entries could be:

1,9
1,2,3,11,6,13
6

it is Type:=3 = number (1) + text(2)

And how can I take this "set" of entries from the InputBox and list them in a column as individual numbers. (That range will be Dim IndexLibary As Range("XX:YY")in the code)

try:

Sub Test()
Dim strIn As String
Dim varOut As Variant
Dim myCount As Integer

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
If myCount = 0 Then
[A1] = strIn
Else
varOut = Split(strIn, ",")
Cells(1, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If
End Sub


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

Hi Howard,

Am Sun, 14 Jul 2013 18:03:23 +0200 schrieb Claus Busch:

it is Type:=3 = number (1) + text(2)

you also can do it with an array (Type:=64) but then you have to enter
more than one number as array with curled brackets ({1,3,5,7})
I did it here with comma but I don't know your delimiter for arrays.
A single number don't need the brackets.

Sub Test2()
Dim varOut As Variant

varOut = Application.InputBox("Enter a single number or " _
& "an array of numbers", Type:=64)

If UBound(varOut) = 1 Then
[A1] = varOut
Else
Cells(1, 1).Resize(UBound(varOut), 1) = _
WorksheetFunction.Transpose(varOut)
End If
End Sub


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

On Sunday, July 14, 2013 9:23:55 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 14 Jul 2013 18:03:23 +0200 schrieb Claus Busch:



it is Type:=3 = number (1) + text(2)



you also can do it with an array (Type:=64) but then you have to enter

more than one number as array with curled brackets ({1,3,5,7})

I did it here with comma but I don't know your delimiter for arrays.

A single number don't need the brackets.



Sub Test2()

Dim varOut As Variant



varOut = Application.InputBox("Enter a single number or " _

& "an array of numbers", Type:=64)



If UBound(varOut) = 1 Then

[A1] = varOut

Else

Cells(1, 1).Resize(UBound(varOut), 1) = _

WorksheetFunction.Transpose(varOut)

End If

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The first suggested code looks like it is best suite for where I'm going with this 'lil project.

Works good. I can read it for the most part but danged if I can write like that.

Thanks.
Howard

Hi Howard,

Am Sun, 14 Jul 2013 09:32:15 -0700 (PDT) schrieb Howard:

The first suggested code looks like it is best suite for where I'm going with this 'lil project.

glad to help and that the code works for you


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

On Sunday, July 14, 2013 9:34:30 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 14 Jul 2013 09:32:15 -0700 (PDT) schrieb Howard:



The first suggested code looks like it is best suite for where I'm going with this 'lil project.



glad to help and that the code works for you





Regards

Claus B.

Hi Claus,

Indeed it works fine until it gets to the msgbox and there I have messed something up, can't seem to get c to behave for me.

Just want to take each value in IndexLibry and if a match is in IndexCol then do the MyStringVariable thing to the text box.

Howard

Option Explicit

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

Dim strIn As String
Dim varOut As Variant
Dim myCount As Integer

Set IndexCol = Range("A2:A12")
Set IndexLibry = Range("A15:A24")

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
IndexLibry.ClearContents

If myCount = 0 Then
[A15] = strIn
Else
varOut = Split(strIn, ",")
Cells(15, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If

For Each c In IndexLibry

MsgBox "After here = Type Mismatch"

If c.Value = IndexCol.Value Then
MyStringVariable = c.Offset(0, 4) & ", " & c.Offset(0, 1) _
& ", " & c.Offset(0, 2) & ", " & c.Offset(0, 14) _
& ", " & c.Offset(0, 15) & ", " & c.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If

I believe this to be the link


https://www.dropbox.com/s/rjcbl1cljv...p %20Box.xlsm

Howard

Hi Howard,

Am Sun, 14 Jul 2013 12:45:35 -0700 (PDT) schrieb Howard:

https://www.dropbox.com/s/rjcbl1cljv...p %20Box.xlsm

you have to lookat xlwhole and only in cells with values.
Try the loop:

For Each c In IndexLibry
Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)
If Not rngC Is Nothing And rngC < "" Then
MyStringVariable = rngC.Offset(0, 4) & ", " & rngC.Offset(0, 1) _
& ", " & rngC.Offset(0, 2) & ", " & rngC.Offset(0, 14) _
& ", " & rngC.Offset(0, 15) & ", " & rngC.Offset(0, 18)

ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If
Next


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

On Sunday, July 14, 2013 1:18:20 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 14 Jul 2013 12:45:35 -0700 (PDT) schrieb Howard:



https://www.dropbox.com/s/rjcbl1cljv...p %20Box.xlsm



you have to lookat xlwhole and only in cells with values.

Try the loop:



For Each c In IndexLibry

Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)

If Not rngC Is Nothing And rngC < "" Then

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

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

& ", " & rngC.Offset(0, 15) & ", " & rngC.Offset(0, 18)



ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _

& vbCr & MyStringVariable

End If

Next





Regards

Claus B.

A work of art!!
Thanks so much.

Regards,
Howard

Hi Howard,

Am Sun, 14 Jul 2013 13:26:54 -0700 (PDT) schrieb Howard:

Thanks so much.

please have a look:
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the folder "Howard".


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

Not meaning to take away from Claus' suggestion, but here's how I'd
handle the input so it's easier to maintain...

Sub ListNums()
Dim vNums
vNums = Application.InputBox("Enter a number" _
& vbCrLf & "OR" & vbCrLf _
& "a list of numbers separated by commas", Type:=3)
vNums = Split(vNums, ",")
If UBound(vNums) = 0 Then
[A1] = vNums
Else
[A1].Resize(UBound(vNums) + 1, 1) _
= WorksheetFunction.Transpose(vNums)
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, July 14, 2013 4:09:19 PM UTC-7, GS wrote:
Not meaning to take away from Claus' suggestion, but here's how I'd

handle the input so it's easier to maintain...



Sub ListNums()

Dim vNums

vNums = Application.InputBox("Enter a number" _

& vbCrLf & "OR" & vbCrLf _

& "a list of numbers separated by commas", Type:=3)

vNums = Split(vNums, ",")

If UBound(vNums) = 0 Then

[A1] = vNums

Else

[A1].Resize(UBound(vNums) + 1, 1) _

= WorksheetFunction.Transpose(vNums)

End If

End Sub

Garry


Hi Garry,

Thanks for the input on the input (pun intended)

I copied your and Claus' codes and posted the in a sheet module to better compare the scripts.

Of course you and Claus could have a meaningful discussion on the codes between yourselves, but the finer points, sadly, would be lost to me.

I can take the codes offered and know what they will do because I ask "How do you...?" and get amazing (to me) answers, plug them in and away I go on to the next hurdle.

I have picked up quite a bit from you and Claus and others, and it stokes my keeps-me-off-the-streets Excel hobby.

Always appreciate the help.

Regards,
Howard

Yes, Claus provides some pretty awesome solutions. Glad I'm able to
contribute as well! (Don't want you becoming a 'street person' now do
we?)<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion