| |
こんなで
Option Explicit
Public Sub Sample_2()
Const cstrChar As String = "部:"
Dim i As Long
Dim vntData() As Variant
Dim strData() As Variant
Dim N() As Variant
Dim vntNumb As Variant
' Dim N() As String '★文字列として代入の場合
Dim lngPos As Long
With ActiveSheet
vntData = Range(.Cells(1, "B"), .Cells(Rows.Count, "B").End(xlUp)).Value
End With
ReDim strData(1 To UBound(vntData, 1), 1 To 1)
ReDim N(1 To UBound(vntData, 1), 1 To 1)
For i = 1 To UBound(vntData, 1)
lngPos = InStr(1, vntData(i, 1), cstrChar, vbTextCompare)
If lngPos > 0 Then
vntNumb = Val(Mid(vntData(i, 1), lngPos + Len(cstrChar)))
strData(i, 1) = CStr(vntNumb)
Else
strData(i, 1) = CStr(vntData(i, 1))
N(i, 1) = vntNumb
End If
Next i
With ActiveSheet
.Cells(1, "B").Resize(UBound(strData, 1)).Value = strData
.Cells(1, "N").Resize(UBound(strData, 1)).Value = N
End With
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
- 10/11/14(日) 18:10 -
