CODEWITHSUNDEEP
excelvba
user4392273
user4392273

Reputation:

creating an array from the value of a single cell in excel

I have a spreadsheet where I want to have: 1) A source cell, which will have a string such as D2594D8-8. You can see this is a string of hexadecimal digits without a delimiter except the single dash

2)a group of label and "target" cells where a macro will copy each individual hex digit from the source cell individually.

So an example would be:

Step 1: Enter into D1 the value: D2594D8-8

Step 2: Run the macro

Step 3: the values of the cells: C4 updated to equal "D" (The first character from the source cell string) D4 updated to equal "2" (The second character from the source cell string) E4 updated to equal "5" (The thrid character from the source cell string) etc....

I currently am trying:

Sub AssignData()

Dim wldData As Variant
UWParray = Array(Range("D1"))

Range("D4").Value = UWParray(0)
Range("D5").Value = UWParray(1)
Range("D6").Value = UWParray(2)
Range("D7").Value = UWParray(3)

End Sub

But that only gets me: "Run-time error '9' Subscript out or Range

and the result:

1 D2594D8-8 2
3
4
5
6
7

Any help would be appreciated!

Thanks in advance

Upvotes: 2

Views: 3650

Answers (3)

brettdj
brettdj

Reputation: 55682

A one liner :)

[c4].Resize(1, Len([d1].Value)) = Application.Transpose(Evaluate("=index(mid(D1,ROW(1:" & Len([d1].Value) & "),1),)"))

enter image description here

Upvotes: 2

Hubvill
Hubvill

Reputation: 504

This should do what your asking:

Dim my_array() As String
Dim my_String As String
Dim i As Integer

my_String = Range("D1").Value

'Replace "-" with nothing
my_String = Replace(my_String, "-", "")

'Split my string into individual characters and store in array/worksheet
ReDim my_array(Len(my_String) - 1)

For i = 1 To Len(my_String)
    my_array(i - 1) = Mid(my_String, i, 1)
    'Store values in excel sheet starting at C3
    Cells(4, (2 + i)).Value = my_array(i - 1)
Next

You actually don't need to use an array to store the values into the worksheet's cells, but I added it because of the post title.

Upvotes: 1

nwhaught
nwhaught

Reputation: 1592

Your code is taking the entire D1 value and putting it into the first position of the array, so when it goes to look for the second position, it doesn't exist--hence the "subscript out of range" error. The below code works.

    Sub AssignData()

Dim wldData As Variant
Dim UWParray() As String
Dim i As Integer

ReDim UWParray(Len(Range("D1").Value))

For i = 0 To Len(Range("D1").Value)
    UWParray(i) = Mid(Range("D1").Value, i + 1, 1)
Next

Range("D4").Value = UWParray(0)
Range("D5").Value = UWParray(1)
Range("D6").Value = UWParray(2)
Range("D7").Value = UWParray(3)

End Sub

Upvotes: 3

Related Questions