Redim Preserve doesnt work for me.

Eric Carolus

Board Regular
Joined
Sep 17, 2012
Messages
128
Office Version
  1. 2016
Platform
  1. 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.


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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You haven't identified which line gives you the error, but I'm guessing it's happening here:

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

Why are you hard-coding the upper bound 93 when your Redim Preserve may have made the array(s) smaller?
 
Upvote 0
Solution
Hi StephenCrump
Thanks for the reply.
For some or other reason my code does not indicate which line gives the error.
How do I correct that?

How should I correct the upper bound because I suspect that might be my problem?
Thanks
Crow
 
Upvote 0
Hi StephenCrump

Your question had me thinking and so I changed
For i = 1 To 93 To For i = 1 To UBound(myArray)
and
For z = 1 To 93 to For z = 1 To UBound(myArrayper)

And it wooooooorks like a charm!

The other question remains though!
Thank you
crow
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top