|
みなさまありがとうございました!
やっちんさんに教えていただいたDim i As Long に変えて、
下のコードの最後にNextを加えたらきちんと作動しました。
'===============
'在庫数による文字列操作
Select Case C.Value
Case Is = "": St = St
Case Is < 2: St = St & Bs & "/00,"
Case Is < 6: St = St & Bs & "/01,"
Case Is < 10: St = St & Bs & "/02,"
Case Else: St = St & Bs & "/99,"
End Select
Next ←ここにNext
'===============
実のところ、Nextは変数と組み合わせて使うものと思って
いましたので、なぜ Next iじゃないのか自分でもよくわかりません(??)
ハトさんご指摘いただいた sizeIndex の格納ですが、
下のコードは必要ないものなので、削除しました。
> For cnt = 0 To (UBound(sizeIndex))
> tmpData = dic(sizeIndex(cnt))
> If Not IsNull(tmpData) And tmpData <> "" Then
> St = St & tmpData & ","
> End If
> Next
下のコードで作動しました。
Sub 在庫()
Dim C As Range
Dim i As Long, v As Integer
Dim Bs As String, St As String
Dim lngEndROW As Long
Dim test As Variant, sizeIndex As Variant
'ターゲットシート指定
shName = "アクセサリー"
'サイズ表記の操作
With Worksheets("アクセサリー")
For v = 38 To 5 Step -1
test = .Cells(1, v).Value
If Not IsNumeric(test) Then
If test = "総計" Or test = "(空白)" Then
.Columns(v).Delete xlShiftToLeft
End If
Else
If Len(test) = 4 Then
.Cells(1, v).Value = _
Left(test, 2) & "-" & Right(test, 2)
.Cells(1, v).Value = VBA.Trim(.Cells(1, v).Value)
ElseIf Len(test) = 3 Then
.Cells(1, v).NumberFormat = "@"
.Cells(1, v).Value = _
Left(test, 2) & "." & Right(test, 1)
End If
End If
Next v
End With
'特殊サイズの置換・修正
Worksheets(shName).Range("E1:AJ1").Replace what:="_", replacement:="", lookat:=xlPart
Worksheets(shName).Range("E1:AJ1").Replace what:="OSFX", replacement:="OS-FX", lookat:=xlPart
Worksheets(shName).Range("E1:AJ1").Replace what:="OSFZ", replacement:="OS-FZ", lookat:=xlPart
'最終行取得
lngEndROW = Worksheets(shName).Range("A65536").End(xlUp).Row
For i = 2 To lngEndROW
If lngEndROW < 2 Then Exit Sub
Bs = "": St = ""
For Each C In Worksheets(shName).Cells(i, 5).Resize(, 36)
Bs = Trim(Worksheets(shName).Cells(1, C.Column).Text)
'在庫数による文字列操作
Select Case C.Value
Case Is = "": St = St
Case Is < 2: St = St & Bs & "/00,"
Case Is < 6: St = St & Bs & "/01,"
Case Is < 10: St = St & Bs & "/02,"
Case Else: St = St & Bs & "/99,"
End Select
Next
'AM列にサイズ値の代入
Worksheets(shName).Range("AM:AM").NumberFormat = "@"
St = Left$(St, Len(St) - 1)
Worksheets(shName).Cells(i, 39).Value = Trim(St)
Next i
End Sub
|
|