Offset problem
Patrick:
You need to get the rows of the range that is currently selected:
lRowStart = Selection.row
lRowEnd = lRowStart + selection.rows.count - 1
and the column
lColumn = selection.column + 26
and then you can use
range(cells(lRowStart,lColumn), cells(lRowEnd,lColumn)).Value
= ListBox1.List(ListBox1.ListIndex, 4)
Private Sub OK_Click()
Dim lRowStart As Long, lRowEnd As Long, lColumn As Long
ThisWorkbook.Activate
On Error GoTo Sub1
lRowStart = Selection.row
lRowEnd = lRowStart + selection.rows.count - 1
lColumn = selection.column + 26
Selection = ListBox1.List(ListBox1.ListIndex, 0)
Range(cells(lRowStart,lColumn), cells(lRowEnd,lColumn)).Value _
= ListBox1.List(ListBox1.ListIndex, 4)
ListBox1.ListIndex = 0
Sub1:
Unload EmployeeList
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"Patrick Simonds" wrote:
As you can tell I use the code to place the contents of a Listbox into the
active cell and a cell 27 cells to the right. If I select a range of cells
(say A1 - E1) the ListBox content is placed in all selected cells but only
once in the cell 27 to the right of the first cell selected. I want a value
placed in the 27th cell from each of the selected cells.
Private Sub OK_Click()
ThisWorkbook.Activate
On Error GoTo Sub1
Selection = ListBox1.List(ListBox1.ListIndex, 0)
ActiveCell(1, 27).Value = ListBox1.List(ListBox1.ListIndex, 4)
ListBox1.ListIndex = 0
Sub1:
Unload EmployeeList
End Sub
|