Eric Carolus
Board Regular
- Joined
- Sep 17, 2012
- Messages
- 128
- Office Version
- 2016
- Platform
- Windows
Hi folks
I really need some help though I have searched on the forum.
Some background to my problem. I have a range of string values (93) with their corresponding integer values (93).
With the push of a button on the worksheet, ALL the string values and corresponding integer values are written to two arrays.
The string values and integer values are written to a msgbox and finally written to a form.
However, whatever is written to the two arrays, depends on the LENGTH OF THE STRING VALUES.
The length of the string values will finally determine the length of the array. If the one string value (of the 93) is <2 or >2 then that
value will be omitted from the array, thus producing an array length of 92., and so on and so on,
Everything works well, i.e. It shows the array length of the string values, it shows the array length of the integer values
It shows the array elements of the string values and the integer values, and then it says:
runtime error 9 - subscript out of range.
As a result the array string and integer values are never written to the form.
I will appreciate any and all help. The code follows.
Thanks you folks
Crow
I really need some help though I have searched on the forum.
Some background to my problem. I have a range of string values (93) with their corresponding integer values (93).
With the push of a button on the worksheet, ALL the string values and corresponding integer values are written to two arrays.
The string values and integer values are written to a msgbox and finally written to a form.
However, whatever is written to the two arrays, depends on the LENGTH OF THE STRING VALUES.
The length of the string values will finally determine the length of the array. If the one string value (of the 93) is <2 or >2 then that
value will be omitted from the array, thus producing an array length of 92., and so on and so on,
Everything works well, i.e. It shows the array length of the string values, it shows the array length of the integer values
It shows the array elements of the string values and the integer values, and then it says:
runtime error 9 - subscript out of range.
As a result the array string and integer values are never written to the form.
I will appreciate any and all help. The code follows.
VBA Code:
Private Sub UserForm_Initialize()
'HideBar Me
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''WRITTEN TO AN ARRAY. WORKS LIKE A CHARM!!!!!!!!! 2
'''ARRAY FOR ONLY THE TEACHERS'AND PERIODS
Dim myArray() As Variant, myArrayper()
Dim DataRange As Range, DataRangeper
Dim cell As Range, cellper
Dim x As Long, x2
Dim ws As Worksheet
Dim r As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
'Determine the data you want stored
Set DataRange = ws.Range("AZ19:AZ28,BB19:BB28,BD19:BD28,BF19:BF28,BH19:BH28,BJ19:BJ28,BL19:BL28,BN19:BN28,BP19:BP28,BR19:BR21") '' 'TEACHERS
Set DataRangeper = ws.Range("BA19:BA28,BC19:BC28,BE19:BE28,BG19:BG28,BI19:BI28,BK19:BK28,BM19:BM28,BO19:BO28,BQ19:BQ28,BS19:BS21") ''
'Resize Array prior to loading data
ReDim myArray(DataRange.Cells.Count)
ReDim myArrayper(DataRangeper.Cells.Count)
'Loop through each cell in Range and store TEACHER valueS in Array
For Each cell In DataRange.Cells
If Len(cell.Value) < 2 Or Len(cell.Value) > 2 Then
myArray(x) = ""
Else
myArray(x) = cell.Value
x = x + 1
End If
Next cell
Dim lNumElements As Long
ReDim Preserve myArray(0 To x) 'WORKS CURVE BALL
'ReDim Preserve myArray(x) 'WORKS CURVE BALL
lNumElements = UBound(myArray) - LBound(myArray) ''+ 1
MsgBox lNumElements & " Really?"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each cellper In DataRangeper.Cells
If Len(cellper.Offset(0, -1).Value) < 2 Or Len(cellper.Offset(0, -1).Value) > 2 Then
myArrayper(x2) = ""
Else
myArrayper(x2) = cellper.Value
x2 = x2 + 1
End If
Next cellper
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lNumElementsper As Long
ReDim Preserve myArrayper(0 To x2)
lNumElementsper = UBound(myArrayper) - LBound(myArrayper) ''+ 1
MsgBox lNumElementsper & " Oh Yeah??"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
MsgBox Join(myArray) & vbCrLf _
& vbCrLf & Join(myArrayper)
'' WRITE VALUES TO FORM
Dim z As Long
For i = 1 To 93
Me.Controls("lblc" & i).Caption = myArray(i - 1)
Next i
For z = 1 To 93
frmallteachers.Controls("lblp" & z).Caption = myArrayper(z - 1)
Next z
End Sub
Thanks you folks
Crow