(VBA) Code To Load An Array With Sequential Numbers.

[kelly mort]

Sub LoopThroughArray()
  'create array
  Dim strName() As Variant 
  strName() = Array(1, 2, 3, 4, 5, 6) 

Dim strFind as string 
strFind = 20

Dim i As Long
'loop through the array
   For i = LBound(strName, 1) To UBound(strName, 1)
       If InStr(strName(i), strFind) > 0 Then
          MsgBox "Number has been found!"
          Exit For
       End If
    Next i
End Sub

My goal is to be able to find a way to load 1 up to 100 or any upper limit without doing all the numbering manually.

strName() = Array(1, 2, 3, 4, 5, 6)

Please I need help.

Thanks

 

[Fluff]

How about

  strName() = [transpose(row(1:100))]

 

[Rick Rothstein]

How about

  strName() = [transpose(row(1:100))]

And, if you know what the column letter designation of Column 100 is (it is CV), you can shorten this to...

strName() = [column(a:cv)]

 

[Fluff]

If you want to specify the upper limit each time, you can use

  Dim x As Variant
  x = InputBox("Please enter upper limit")
  If x = "" Then Exit Sub
  strName() = Evaluate("transpose(row(1:" & x & "))")

 

[kelly mort]

Your line worked like a charm – but I think I have some issues with my code which is not giving me the exact outcome I am looking for (independent of what I asked before).



This is how I am implementing the code in my workbook:

If Len(CmbAss1) Then

ConvPercent = Split(CmbAss1, "-")

ConvPer = Val(ConvPercent(0))

Select Case ConvPer

Case 20: strName() = [transpose(row(1:20))]

Case 30: strName() = [transpose(row(1:30))]

Case 40: strName() = [transpose(row(1:40))]

Case 50: strName() = [transpose(row(1:50))]

End Select



For i = 0 To 9

For Each cScore In sh.Range("I7:I" & lr).Offset(, i).Cells

If cScore <> "" Then

strFind = Val(cScore)

For j = LBound(strName, 1) To UBound(strName, 1)

If InStr(strName(j), strFind) = 0 Then

MsgBox "Sorry, " & cScore & ", is outside your selection", vbExclamation, ""

CmbAss1 = ""

Exit Sub

End If

Next j

End If

Next cScore

Next i

End If

Inside the CmbAss1 combobox, I will be making a selection which could be any of the 4:

20-80

30-70

40-60

50-50



Then I had the split function to split and take the first part as shown with my case statement above.

I will be looping through 10 columns. And in each column, I wanna check if there is a number which does not fall with the ConvPer value.



This part of the code:

For j = LBound(strName, 1) To UBound(strName, 1)

If InStr(strName(j), strFind) = 0 Then

MsgBox "Sorry, " & cScore & ", is outside your selection", vbExclamation, ""

CmbAss1 = ""

Exit Sub

End If

Next j

Is something I found on the web – I don’t really understand it and I am thinking I failed to tweak it to my needs. Is there something I am not able to spot?



Thanks in advance.

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks

 

[kelly mort]

As this is a totally different question, it needs a new thread. Thanks

okay thanks

 

[JenniferMurphy]

How about

  strName() = [transpose(row(1:100))]

Is this standard VBA syntax? I entered that string into the Immediate window and got an error.

I, too, would like a way to generate an array of sequential numbers from 1 to N in VBA.

 

[Peter_SSs]

I, too, would like a way to generate an array of sequential numbers from 1 to N in VBA.

You have more resources available than the OP

Sub myArray()
  Dim arr() As Variant
  Const N As Long = 100
  
  arr = Evaluate("sequence(," & N & ")")
End Sub

 

[JenniferMurphy]

You have more resources available than the OP

Sub myArray()
  Dim arr() As Variant
  Const N As Long = 100
 
  arr = Evaluate("sequence(," & N & ")")
End Sub

I just tried this, which gave me a one dimensional array (we used to call them vectors) with 5 elements. Do I need the Evaluate?

Dim seq As Variant
seq = Application.Sequence(1, 5, 1, 1)