PDA

View Full Version : [SOLVED] Help needed to assign multiple values to multiple variables



Poundland
11-11-2013, 08:35 AM
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
11-11-2013, 08:55 AM
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
11-11-2013, 09:06 AM
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
11-11-2013, 09:28 AM
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
11-11-2013, 09:30 AM
Try using

Dim dep As Variant

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


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

shrivallabha
11-11-2013, 09:49 AM
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
11-12-2013, 01:59 AM
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
11-12-2013, 04:24 AM
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
11-12-2013, 06:27 AM
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
11-12-2013, 06:42 AM
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
11-12-2013, 08:44 AM
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.