| |
こんなので善いと思いますが？
多分、LinkedCellがB17に成っているのを消して下さい
Option Explicit
Private Sub ComboBox1_Change()
Dim lngRowEnd As Long
Dim lngRow As Long
'ComboBox1のListIndexを取得
lngRow = ComboBox1.ListIndex
'ComboBoxで選択されていない場合
If lngRow = -1 Then
Exit Sub
End If
'最終行位置を取得(B列を最下行から上にデータの在る位置を取得)
lngRowEnd = Me.Cells(Rows.Count, "B").End(xlUp).Row
If IsEmpty(Me.Cells(lngRowEnd, "B")) Then
lngRowEnd = 1
Else
lngRowEnd = lngRowEnd + 1
End If
'転記元行位置を取得
'※ComboBoxのListIndexが転記元Listの先頭行からの行Offsetを示す
'※因って、ListIndexに転記元Listの先頭行行位置を加算すれば転記Listの行が解る
'ListFillRangeが例えば「Sheet2!A5:J7」ならA5の5
lngRow = ComboBox1.ListIndex + 5
'転記（値の場合）
With Worksheets("Sheet2")
Me.Cells(lngRowEnd, "B").Value = .Cells(lngRow, "A").Value
Me.Cells(lngRowEnd, "C").Value = .Cells(lngRow, "B").Value
Me.Cells(lngRowEnd, "D").Value = .Cells(lngRow, "C").Value
Me.Cells(lngRowEnd, "E").Value = .Cells(lngRow, "D").Value
Me.Cells(lngRowEnd, "F").Value = .Cells(lngRow, "E").Value
Me.Cells(lngRowEnd, "H").Value = .Cells(lngRow, "F").Value
Me.Cells(lngRowEnd, "J").Value = .Cells(lngRow, "G").Value
Me.Cells(lngRowEnd, "K").Value = .Cells(lngRow, "H").Value
Me.Cells(lngRowEnd, "P").Value = .Cells(lngRow, "I").Value
Me.Cells(lngRowEnd, "Q").Value = .Cells(lngRow, "J").Value
End With
End Sub
|
|
Hirofumi
- 10/2/15(月) 17:13 -
