Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset problem
This does not seem to put the value in all the offset cells, only the first
one. "Martin Fishlock" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset problem
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. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Patrick Simonds" wrote: This does not seem to put the value in all the offset cells, only the first one. "Martin Fishlock" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset problem
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 "Martin Fishlock" wrote in message ... 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. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Patrick Simonds" wrote: This does not seem to put the value in all the offset cells, only the first one. "Martin Fishlock" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset problem
Selection.value = ListBox1.List(ListBox1.ListIndex, 0)
Selection.offset(0, 26).Value = ListBox1.List(ListBox1.ListIndex, 4) -- Regards, Luc. "Festina Lente" "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset VBA Problem | Excel Discussion (Misc queries) | |||
Problem with an offset | Excel Programming | |||
Offset Problem | New Users to Excel | |||
Help with OFFSET Problem Please | Excel Programming | |||
OFFSET problem | Excel Programming |