Trying to create Array. It worked once then stopped.

[bteddy1]

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.

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

 

[eduzs]

Where's the rest of the code?

 

[bteddy1]

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?

 

[mikerickson]

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

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.

 

[eduzs]

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.

 

[bteddy1]

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)"

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.

 

[eduzs]

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?