Solved: Assign Cell Value to array

[pratyu]

Hello All!
I'm trying to write a code which will assign the value of a cell to an array...
Following is my code snippet:
Dim c As Variant
Dim aypartList() As Variant 'array of partnumbers
Dim ayCount As Integer
ayCount = 0
Do
c.Select
aypartList(ayCount) = c.Value 'Type mismatch error 13 shown in this line
' Im trying to assign the value of cell "c.value" to aypartList(0) etc
ayCount = ayCount + 1
Loop
ReDim Preserve aypartList(0 To ayCount) As Variant
MsgBox aypartList(ayCount)

I'd like to know where i went wrong...!!
Thanks a lot in advance!!

[Kenneth Hobs]

Please use VBA code tags. See if this clears it up for you.

Sub t()
Dim a() As Variant, c As Range
a() = Array(1, 2, 3)
MsgBox Join(a, vbLf)
Set c = Range("A1")
c.Value2 = 4
ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = c.Value2
MsgBox Join(a, vbLf)
End Sub

[CodeNinja]

pratyu,
As Kenneth has pointed out, you are not "re-dimming" the array. When you get to the line aypartList(ayCount) = c.Value 'Type mismatch error 13 shown in this line, there are no parameters set for aypartlist() ...
You need a line to set those parameters such as: redim aypartlist(1 to aycount)
If you redim preserve (like Kenneth did above), you will not loose any data in that array.

[pratyu]

Kenneth,

I am looking out for an option which "assigns value of a cell to an array_name(index)variable"

Can you please elaborate on the code, you've provided.. I'm a Newbie in VBA :)

CodeNinja,

The code I mentioned seems to look the same way as you have suggested...

Am I ignoring any point? Please clarify..

Thanks to both, for immediate responses!! :)

[Kenneth Hobs]

Press F1 in or near a command word to get specific help in the VBE. Obviously, UBound() gives the upper boundary index of the array as LBound() gives the lower index boundary index.

Just run the macro in a blank sheet to see what it is doing or run in debug mode by pressing F8 to see each line executed. Hover mouse over parts to see values. View > Locals will show you information as you Debug each line as well.

Example 2:
Option Explicit

Option Base 0 'The default. Use 1 if you want first element index to be 1. Split() is always 0 based.

Sub t()
Dim a() As Variant, c As Range
a() = Array(1, 2, 3)
MsgBox Join(a, vbLf)
Set c = Range("A1")
c.Value2 = a(1) '2nd element of a() since a() is 0 based.
ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = c.Value2
MsgBox Join(a, vbLf)
End Sub

[CodeNinja]

pratyu,
Sorry I was not clear...

The first time you iterate through the code, you have not set aypartList() to have parameters... you need to redim it BEFORE using it... so...


Dim c As Variant
Dim aypartList() As Variant 'array of partnumbers
Dim ayCount As Integer

redim aypartList(1) ' <---- This is needed
ayCount = 0
Do
c.Select
aypartList(ayCount) = c.Value 'Type mismatch error 13 shown in this line
' Im trying to assign the value of cell "c.value" to aypartList(0) etc
ayCount = ayCount + 1
Loop
ReDim Preserve aypartList(0 To ayCount) As Variant
MsgBox aypartList(ayCount)



There are other problems with this code, but I am assuming you handled them outside of the snippet you provided.

Hope this helps.

[pratyu]

Kenneth,

Thanks a lot!

[CodeNinja]

And listen to Kenneth... he is a much more experienced coder than I am...

[pratyu]

CodeNinja,

I've done as you've said... While doing this, I'm getting an error as follows

"Run time error 424: Object Required" at the line

aypartList(ayCount) = c.Value

[pratyu]

Yes yes... I'm working on his code... I'm modifying it to merge into mine...

[CodeNinja]

Have you declared what c is??? I think that is one of the issues I found that I assumed you took care of...

so according to the code you have, you:
1 - Dim c as variant
2 - dim and redim the array
3 - try to put value of c into the array...

Where did you tell the program what c is???

Is c a range? If so you probably should dim it as a range not a variant...
Where do we get c from???

dim c as range
set c = sheet1.cells(aycount,1) 'or where ever you want to get c from... I can't tell this from the limited info you have provided.

Hope this helps

[Tinbendr]

Crossposted (http://www.mrexcel.com/forum/showthread.php?642768-Assign-cell-value-to-an-array&p=3189941#post3189941)

Please do not post the same question to multiple boards UNLESS you provide a link. It is considered very rude!

[pratyu]

Tinbender,

Sorry!! I'm very new to these forums..!! So, I dont knw how things go here...!
Anyway, ill keep this in my mind!

[pratyu]

All,

I got my problem fixed! Thanks a lot guys!

[Kenneth Hobs]

See this link for details about cross-posting for the next time that you consider it. http://www.excelguru.ca/content.php?184

[pratyu]

Alright Kenneth,...!!

Thanks..!
Sorry for the offence!

[Kenneth Hobs]

It is not a problem. Most everyone does that when they first need help and use the forums. We just like people to know how to get the best use of the forums.

cheers