Trying to create Array. It worked once then stopped.

bteddy1

New Member
Joined
Jun 27, 2020
Messages
5
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
695
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Where's the rest of the code?
 

bteddy1

New Member
Joined
Jun 27, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,992
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.
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
695
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 27, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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:

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
695
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,130,244
Messages
5,641,055
Members
417,190
Latest member
Sdwd76

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