Help with issue changing value of an array entry

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
Hello all,

Here is my situation, i have code that creates an array based on values from a column. Now in order for the code to work that i'm creating the array for, each entry length in the array must 11. Now I did a code to make this happen in sense but it's not working. It's giving me an error of out of range. Here is the code:

VBA Code:
Sub ExistArray()

Dim MAINws As Worksheet
Dim lrowMain As Long
Dim Cnt As Long

Set MAINws = ThisWorkbook.Worksheets("Assessments")    'Previous Assessment District Worksheet

lrowMain = MAINws.Cells(Rows.Count, "C").End(xlUp).Row

EArr = MAINws.Range("C2:C" & lrowMain).Value

For Cnt = 1 To UBound(EArr)
    Select Case Len(EArr(Cnt, 1))
        Case 10
            EArr(Cnt) = "0" & CStr(LCase(EArr(Cnt)))
        Case 9
            EArr(Cnt) = "00" & CStr(LCase(EArr(Cnt)))
    End Select
Next Cnt

End Sub

As you can see the Array is simple, the smallest value length that EArr entry is 9. Please let me know what i'm doing wrong. Thank you.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,487
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe...

VBA Code:
For Cnt = 1 To UBound(EArr)
    Select Case Len(EArr(Cnt, 1))
        Case 10
            EArr(Cnt, 1) = "0" & CStr(LCase(EArr(Cnt, 1)))
        Case 9
            EArr(Cnt, 1) = "00" & CStr(LCase(EArr(Cnt, 1)))
    End Select
Next Cnt

Hope this helps

M.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,674
Members
415,920
Latest member
ExcelNoob28

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