Help needed to assign multiple values to multiple variables

[Poundland]

I have declared some variables, as below;

Dim dep(1 To 112) As String

I am trying to tell VBA to assign different values to each variable using a For Next Loop. but I am having a some difficulty in writing the code that will assign a value to each of the variables.

My code is below, but it does not work...

Sheets("Department Correlations NEW").Select
Cells(2, 2).Select
For a = 1 To 112
Selection.Offset(0, 1).Select
dep(a) = ActiveCell.Value
Next a

I could write out 112 lines of code and assign the values that way but figured there must be a quicker way of doing this.

Thanks in advance.

Scott

[Jan Karel Pieterse]

Though inefficient, your code looks OK to me.

NB: lets get the geek words right here :-) You are not assigning values to different variables, you are assigning values to the elements of an Array (dep).

[Poundland]

Jan,

The Code does run through, but when I check the contents of dep(a) it says that it is = <Subscript out of Range>

I also to check, viewed the contents of dep10, and it was empty, so it appears that the code is running through but not assigning any values to the elements!!

[Paul_Hossler]

Make sure you have Option Explicit in the module to avoid missing typos


"The Code does run through, but when I check the contents of dep(a) it says that it is = <Subscript out of Range>" -- I think that if you check dep() after the macro finishs, you'll get that since dep doesn't 'exist' any more

This is not tested

With Sheets("Department Correlations NEW")
    For a = 1 To 112 
         dep(a) = .Cells(2, 2 + a).Value
         Msgbox .Cells(2, 2 + a).Address & " -- " & .Cells(2, 2 + a).Value
    Next a

    Stop   ' Check dep in Watch window now while macro is running

End with

Finally, are you SURE that Selection.Offset(0, 1).Select is doing what you want it to do? This selects the cell to the right


Paul

[Bob Phillips]

Try using

[vba]Dim dep As Variant

dep = Application.Transpose(Application.Transpose(Sheets("Department Correlations NEW").Cells(2, 2).Resize(, 112)))
[/vba]

If item 10 is still empty, maybe your data is in rows not columns.

[shrivallabha]

If you are doing like this then 'a' will have next value which is not there in your array.

Sub test()
Dim dep(1 To 112) As String
Cells(2, 2).Select
For a = 1 To 112
    Selection.Offset(0, 1).Select
    dep(a) = ActiveCell.Value
Next a
MsgBox a
MsgBox dep(a)
End Sub

[Poundland]

I will test all these and let you know if any work and achieve my desired result.

My data that I want to capture is definately in columns, so I know that the Offset command is doing what I need it to do.

This is part of a much larger project, but I know that if I can crack this part then I can apply it wholescale to my project.

I am attempting to write a macro that will produce a Correlation Matrix for some sales data for my company, I have managed it on a much smaller scale with only 17 different data references, but I achieved that by creating 17 lines of code for each element, with 112 and more references, this would be completely impractical.

I will let you know how I get on.

[Poundland]

Make sure you have Option Explicit in the module to avoid missing typos


"The Code does run through, but when I check the contents of dep(a) it says that it is = <Subscript out of Range>" -- I think that if you check dep() after the macro finishs, you'll get that since dep doesn't 'exist' any more

This is not tested

With Sheets("Department Correlations NEW")
    For a = 1 To 112 
         dep(a) = .Cells(2, 2 + a).Value
         Msgbox .Cells(2, 2 + a).Address & " -- " & .Cells(2, 2 + a).Value
    Next a

    Stop   ' Check dep in Watch window now while macro is running

End with

Finally, are you SURE that Selection.Offset(0, 1).Select is doing what you want it to do? This selects the cell to the right


Paul

Paul,

Your code is running of a sort, but it is not capturing and retaining all the variables to the 112 elements of dep.

With Sheets("Department Correlations NEW")
    For a = 1 To 112
        dep(a) = .Cells(2, 2 + a).Value
        MsgBox .Cells(2, 2 + a).Address & " -- " & .Cells(2, 2 + a).Value
        MsgBox (dep1)
    Next a
     
    Stop ' Check dep in Watch window now while macro is running
     
End With

When I run this through, on the first pass dep(a) is assigned with a value, yet when I try to show the value of dep1 it is empty, yet dep(a) and dep1 should be the same!!

I am trying to assign a different value to all 112 elements of dep, your code seems to capture a value in dep(a) but this does translate into the corresponding dep element of dep1 in this example.

[Paul_Hossler]

yet dep(a) and dep1 should be the same!!

I'm missing something here. What is dep1?

I don't see where you assign or even Dim a 'dep1' variable

Paul

[Poundland]

I'm missing something here. What is dep1?

I don't see where you assign or even Dim a 'dep1' variable

Paul

Paul,

I assign dep elements at the very start of the routine;

Dim dep(1 To 112) As String 

I think I have figured out what was wrong with it, my understanding, I was looking for a value in dep1, when watching the assignment of the elements with a routine, I now see that they are being assigned as dep(1).

Thanks for your help, it is much appreciated.

Scott

[Jan Karel Pieterse]

Dep1 is a single variable that can hold a single value. It is completely separate from Dep.

Dep as you declared it is an array variable which holds multiple elements which can be accessed through their array index.

So you will have Dep(1), Dep(2), Dep(3), ... up to Dep(112).

In the VBA editor, click the Help box (top-right of screen) and enter "Arrays" and hit enter. Then choose "Using Arrays" in the list you get to learn more about arrays.