在一个单元格中读取数据并写入另一个单元格

I have a Excel file and I want to read each value within a cell... i.e A cell in column B row 2 may contain ("1 - 10 - 11 - 15 - 17") or ("1-10-11-15-17") with or without the spaces between the hyphen. I want to read each number and save each value as the number 1 in another column on the same row. For example my column B row 2 data is ("1 - 10 - 11 - 15 - 17") or ("1-10-11-15-17") and I want to write a value of 1 on the same row 2 in columns C, L, M, Q, and S as the value of 1. How can I read the data from a cell and write in another cell using a Macro or VBA?

我有一个Excel文件,我想读取单元格中的每个值...即B列第2行中的单元格可能包含(“1 - 10 - 11 - 15 - 17”)或(“1-10-11- 15-17“)带或不带连字符之间的空格。我想读取每个数字并将每个值保存为同一行中另一列中的数字1。例如,我的B列第2行数据是(“1 - 10 - 11 - 15 - 17”)或(“1-10-11-15-17”),我想在同一行2上写入值1在列C,L,M,Q和S中作为值1.如何从单元格读取数据并使用宏或VBA写入另一个单元格?

Thanks in advance for your help, Rose

谢谢你的帮助,罗斯

---

EDIT:

This is just an ex of code

这只是一段代码

Sub Macro1() 
RowCount = Worksheets("2013").UsedRange.Rows.Count 
For i = 2 To RowCount cellvalue = Worksheets("2013").Cells(i, "B").Value 
    If InStr(cellvalue, "1") = 1 Or InStr(cellvalue, "1") > 2 And InStr(cellvalue, "1") < 22 Then 
        Worksheets("2013").Cells(i, "C") = Null 
    ElseIf InStr(cellvalue, "10") <> 0 Then Worksheets("2013").Cells(i, "L") = 1 
    ElseIf InStr(cellvalue, "11") <> 0 Then 
        Worksheets("2013").Cells(i, "M") = 1 
    ElseIf InStr(cellvalue, "12") <> 0 Then 
        Worksheets("2013").Cells(i, "N") = 1 
    Else Worksheets("2013").Cells(i, "C") = Null 
    End If

1 个解决方案

#1

EDIT:

This should do what you want.

这应该做你想要的。

For i = 2 To RowCount
    cellString = Cells(i, 2).Value
    cellString = Replace(cellString, " ", "") 'This finds and removes any spaces from your string
    numbers = Split(cellString, "-") 'Splits remaining values into array delimited by the dash character

    Cells(i, numbers(1)).Value = 1 'Now the column is the number stored in the array
    Cells(i, numbers(2)).Value = 1 'The array starts at index 2 because of the above For loop
    Cells(i, numbers(3)).Value = 1
    Cells(i, numbers(4)).Value = 1
    Cells(i, numbers(5)).Value = 1

Next i