问题描述:

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

网友答案:

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
网友答案:

A one liner :)

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

网友答案:

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.

相关阅读:
Top