Offset problem

[Patrick Simonds]

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

 

[Guest]

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

 

[Patrick Simonds]

This does not seem to put the value in all the offset cells, only the first
one.

 

[Guest]

Patrick,

I just tried it again and it worked for me fine.

Did you clear the old data and select the area?

Try remarking the on error to see if you have an error.

Otherwise post your code and I will have a look again.

 

[Patrick Simonds]

Here is the code, pretty much as you presented it. I then select cells A1
through A3, when the Dialog box comes up I select a name from the Listbox.
For this example the name I select is Jane. Jane's name is placed in cells
A1 through A3. Now the other piece of information which should display is
Jane's status (PTO) and I want it displayed in the 27th cell over from each
of the cells (A1 through A3, which would be AA1 through AC1). What I get is
PTO in cell AA1 only. Hope that make sense.


Private Sub OK_Click()

Dim lRowStart As Long, lRowEnd As Long, lColumn As Long

lRowStart = Selection.Row
lRowEnd = lRowStart + Selection.Rows.Count - 1

lColumn = Selection.Column + 26

Range(Cells(lRowStart, lColumn), Cells(lRowEnd, lColumn)).Value =
ListBox1.List(ListBox1.ListIndex, 4)

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

 

[Guest]

Selection.value = ListBox1.List(ListBox1.ListIndex, 0)
Selection.offset(0, 26).Value = ListBox1.List(ListBox1.ListIndex, 4)