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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
Code:
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.

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

Please I need help.

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
  strName() = [transpose(row(1:100))]
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,626
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
How about
VBA Code:
  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...
VBA Code:
strName() = [column(a:cv)]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
If you want to specify the upper limit each time, you can use
VBA Code:
  Dim x As Variant
  x = InputBox("Please enter upper limit")
  If x = "" Then Exit Sub
  strName() = Evaluate("transpose(row(1:" & x & "))")
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:



Code:
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:

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, it needs a new thread. Thanks
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top