Converting Array to Integers

**cporter5** · 06-28-2010, 05:38 PM

So basically i have an Array called my Array declared as fallowing ...

Please Login or Register  to view this content.

The Array Is populated as fallowing ...

Please Login or Register  to view this content.

What i want it to find a way to turn the Array of Integers into a Single Integer

If

MyArray(1)=1
MyArray(2)=2
MyArray(3)=3
MyArray(4)=4
MyArray(5)=5

then

Please Login or Register  to view this content.

Thank you

**shg** · 06-28-2010, 05:48 PM

Please Login or Register  to view this content.

**Leith Ross** · 06-28-2010, 05:53 PM

Hello cporter5,

Here is another way...

Please Login or Register  to view this content.

**cporter5** · 06-28-2010, 07:40 PM

okay im having trouble with this stupid error .... Runtime error 9

my code looks like this ...

Please Login or Register  to view this content.

The line Password(N)=1 comes up with the out of bounds error '9' ... i dont understand because N = 1 ... and 1 is between 1 and 5 .... this code should store the number 1 Password(1) and then move it so that Password(2) should be ready to accept an input.

**Leith Ross** · 06-28-2010, 08:28 PM

Hello cporter5,

It appears you defined a Worksheet event inside a standard VBA module...

Please Login or Register  to view this content.

N will remain zero because this code will never be run. Cut this code from the module and following the directions below.

How to Save a Worksheet Event Macro

Copy the macro using CTRL+C keys.
Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
Left Click on View Code in the pop up menu.
Paste the macro code using CTRL+V
Make any custom changes to the macro if needed at this time.
Save the macro in your Workbook using CTRL+S

**cporter5** · 06-28-2010, 10:05 PM

it is a module ... that line of code i added in attempt to make it work .... i think the reason that it doesnt work is because N is never given the initial value of 1 ... and i dont know where to put it ... ideal i would like to have N set to 1 somewhere in a Module scope, because i have several procedures that use the N variable ...

**Leith Ross** · 06-28-2010, 10:34 PM

Hello cporter5,

It may be easier to find the problem if you post your workbook for review.

**cporter5** · 06-28-2010, 10:42 PM

Here is my Excel 2007 workbook ... Before the comments start ... i got bored waiting for response so i went a bit over bored on the graphic element ...

**Leith Ross** · 06-28-2010, 10:48 PM

Hello cporter5,

You are correct about the variable not be initialized. The easiest way is to initialize the variable when the workbook first opens. Copy this code into the ThisWorkbook module.

Please Login or Register  to view this content.

**Leith Ross** · 06-28-2010, 11:02 PM

Hello cporter5,

You should declare your variables in Module3 as below:

Please Login or Register  to view this content.

**cporter5** · 06-28-2010, 11:27 PM

i made both of your changes ... but it didnt seem to fix the problem ... when i compile it still comes up with error number 9.... on that same line of code ...

**Leith Ross** · 06-29-2010, 12:05 AM

Hello cporter5,

I don't have Excel 2007 yet, so this workbook is a 2003 version of yours. I get don't error the subscript out range error. I get a error with the Shape properties. Try this out and see how it works.

**cporter5** · 06-29-2010, 12:23 AM

thanks its working now ... is there a line of code that i can add that will reset the macro ... right now the sequence can only be done once ... i would like as soon as its done once ... to be able to do the whole sequence 3 times ... so i would get 3 sets of a 5 digit number ...