Trying to create Array. It worked once then stopped.

bteddy1

New Member
Joined
Jun 27, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Hello,

Sorry, I'm bugging you.

I created an array in excel vba. It worked like it was supposed to. Then stopped. I have been fighting with this all night.
At one point I readded the reference to MSCORLIB,dll to be able to dim varibles as Arrays and array list. It made no difference.

Will you please take a look at the code and tell me if you see anything, Please.

VBA Code:
Function column_irit()
Dim sheet As Worksheet
Dim c As Variant
Dim d As Variant
Dim e As Variant
Dim Letter As String
Dim i As Integer
Dim rng As Range

'Worksheets("Sheet10").Activate

c = Array(“D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”, “AB”, “AC”, “AD”, “AE”, “AF”, “AG”, “AH”, “AI”, “AJ”, “AK”, “AL”, “AM”, “AN”, “AO”, “AP”, “AQ”, “AR”, “AS”, “AT”, “AU”, “AV”, “AW”, “AX”, “AY”, “AZ”, “BA”, “BB”, “BC”, “BD”, “BE”, “BF”, “BG”, “BH”, “BI”, “BJ”, “BK”, “BL”, “BM”, “BN”, “BO”, “BP”, “BQ”, “BR”, “BS”, “BT”, “BU”, “BV”, “BW”, “BX”, “BY”, “BZ”, “CA”, “CB”, “CC”, “CD”, “CE”, “CF”, “CG”, “CH”, “CI”, “CJ”, “CK”, “CL”, “CM”, “CN”, “CO”, “CP”, “CQ”, “CR”, “CS”, “CT”, “CU”, “CV”, “CW”, “CX”, “CY”, “CZ”, “DA”, “DB”, “DC”, “DD”, “DE”, “DF”, “DG”, “DH”, “DI”, “DJ”, “DK”, “DL”, “DM”, “DN”, “DO”, “DP”, “DQ”, “DR”, “DS”, “DT”, “DU”, “DV”, “DW”, “DX”, “DY”, “DZ”, “EA”, “EB”, “EC”, “ED”, “EE”, “EF”, “EG”, “EH”, “EI”, “EJ”, “EK”, “EL”, “EM”, “EN”, “EO”, “EP”, “EQ”, “ER”, “ES”, “ET”, “EU”, “EV”, “EW”, “EX”, “EY”, “EZ”, “FA”, “FB”, “FC”, “FD”, “FE”, “FF”, “FG”, “FH”, “FI”, “FJ”, “FK”, “FL”, “FM”, “FN”, “FO”, “FP”, “FQ”, “FR”, “FS”, “FT”)
i = 1
Letter = c(1)
Do While i <= 57
d = sheet.Cells(c(i) & "2").Value
sheet.Cells(c(i + 1) & "1") = d

Range(c & "2", c & "994").Select
Range(c & "2", c & "994").FillDown

Thank You
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
That's it I was just trying to test the code to see if it would work in the applied application.

The Letter = c(i) was to test the array, to see if the correct letter was selected.
Like I said it worked once then not anymore.

Am I missing something?
 
Upvote 0
Your use of the Cells property isn't correct. It should take two argument your using the array to build cell addresses should work better with the Range property
VBA Code:
d = sheet.Range(c(i) & "2").Value

Also, I noticed that the value of sheet wasn't set before that line occured. Also, its a good idea to avoid variable names that are close to the names of Excel properties or objects. sh is often used as a variable for a Worksheet object.
 
Upvote 0
Try to change the name of the variable "c" to another more unique.
Or, review your code and see if the variable "c" is declared as public, private, global, (outside a sub or a function) in any module etc.
 
Upvote 0
Thank you.
I made some changes but still get errors.
Code attached.

Light Blue is what I changes.
Red comments are the errors.
*ERROR "Subscript out of range (Error 9)
*'Empty Should resolve to "E" for array ref
*ERROR "Object variable not set (Error 91)"

Rich (BB code):
Function column_irit()
Dim Mysheet As Worksheet
Dim z As Variant
Dim d As Variant
Dim e As Variant
Dim Letter As String
Dim i As Integer
Dim rng As Range

Mysheet = Worksheets("Sheet10") 'Get ERROR "Subscript out of range (Error 9)"

z = Array(“D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, _
“S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”, “AB”, “AC”, “AD”, “AE”, “AF”, “AG”, “AH”, “AI”, _
“AJ”, “AK”, “AL”, “AM”, “AN”, “AO”, “AP”, “AQ”, “AR”, “AS”, “AT”, “AU”, “AV”, “AW”, “AX”, _
“AY”, “AZ”, “BA”, “BB”, “BC”, “BD”, “BE”, “BF”, “BG”, “BH”, “BI”, “BJ”, “BK”, “BL”, “BM”, _
“BN”, “BO”, “BP”, “BQ”, “BR”, “BS”, “BT”, “BU”, “BV”, “BW”, “BX”, “BY”, “BZ”, “CA”, “CB”, _
“CC”, “CD”, “CE”, “CF”, “CG”, “CH”, “CI”, “CJ”, “CK”, “CL”, “CM”, “CN”, “CO”, “CP”, “CQ”, _
“CR”, “CS”, “CT”, “CU”, “CV”, “CW”, “CX”, “CY”, “CZ”, “DA”, “DB”, “DC”, “DD”, “DE”, “DF”, _
“DG”, “DH”, “DI”, “DJ”, “DK”, “DL”, “DM”, “DN”, “DO”, “DP”, “DQ”, “DR”, “DS”, “DT”, “DU”, _
“DV”, “DW”, “DX”, “DY”, “DZ”, “EA”, “EB”, “EC”, “ED”, “EE”, “EF”, “EG”, “EH”, “EI”, “EJ”, _
“EK”, “EL”, “EM”, “EN”, “EO”, “EP”, “EQ”, “ER”, “ES”, “ET”, “EU”, “EV”, “EW”, “EX”, “EY”, _
“EZ”, “FA”, “FB”, “FC”, “FD”, “FE”, “FF”, “FG”, “FH”, “FI”, “FJ”, “FK”, “FL”, “FM”, “FN”, _
“FO”, “FP”, “FQ”, “FR”, “FS”, “FT”)
i = 1
Letter = z(1) 'Empty Should resolve to "E"
Do While i <= 57
d = Mysheet.Range(z(i) & "2").Value  'Get ERROR "Object variable not set (Error 91)"

Mysheet.Range(z(i + 1) & "1") = d

Mysheet.Range(c & "2", c & "994").Select
Mysheet.Range(c & "2", c & "994").FillDown
i = i + 1
c = c + 3
Loop
End Function
Thanks again for your time.
 
Last edited by a moderator:
Upvote 0
Try this:
Set Mysheet = Worksheets("Sheet10")
Check if Sheet10 exists.
If the code was stopping at this line, z will be empty.
Why are you using a Function? Why not a Sub?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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