Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Little more advice on this code
Hi all Dave Peterson helped me by writing this code but I need it to do a
little more and I cant seemto work it out. What the code below does isshows the meeting that are coming up and copies the information to another worksheet which I will use another way. But at the moment it only copies the first cell. The list's rowsource is a2:e65536 so I probably need to have the code copy the whole line. Any ideas would be appreciated. Here is the code Private Sub CommandButton1_Click() Dim destCell As Range With Worksheets("Popup") Set destCell = .Columns.End(xlUp).Offset(1, 0) End With With ListBox1 If .ListIndex -1 Then destCell.Value = .List(.ListIndex) End If End With Unload Me messagebox.Show End Sub Thanks again Greg |
#2
|
|||
|
|||
You changed a bit of the code.
This won't work. Set destCell = .Columns.End(xlUp).Offset(1, 0) So you have a listbox that has 5 columns in it. And you want to copy each of the 5 columns to the cell? Something like this might work: Option Explicit Private Sub CommandButton1_Click() Dim destCell As Range Dim iCtr As Long With Worksheets("Popup") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With Me.ListBox1 If .ListIndex -1 Then For iCtr = 1 To .ColumnCount destCell.Offset(0, iCtr - 1).Value _ = .List(.ListIndex, iCtr - 1) Next iCtr End If End With Unload Me End Sub Do you really have almost 64k rows in that listbox? Greg B wrote: Hi all Dave Peterson helped me by writing this code but I need it to do a little more and I cant seemto work it out. What the code below does isshows the meeting that are coming up and copies the information to another worksheet which I will use another way. But at the moment it only copies the first cell. The list's rowsource is a2:e65536 so I probably need to have the code copy the whole line. Any ideas would be appreciated. Here is the code Private Sub CommandButton1_Click() Dim destCell As Range With Worksheets("Popup") Set destCell = .Columns.End(xlUp).Offset(1, 0) End With With ListBox1 If .ListIndex -1 Then destCell.Value = .List(.ListIndex) End If End With Unload Me messagebox.Show End Sub Thanks again Greg -- Dave Peterson |
#3
|
|||
|
|||
You've got a reply at one of your other posts.
Greg B wrote: Hi all Dave Peterson helped me by writing this code but I need it to do a little more and I cant seemto work it out. What the code below does isshows the meeting that are coming up and copies the information to another worksheet which I will use another way. But at the moment it only copies the first cell. The list's rowsource is a2:e65536 so I probably need to have the code copy the whole line. Any ideas would be appreciated. Here is the code Private Sub CommandButton1_Click() Dim destCell As Range With Worksheets("Popup") Set destCell = .Columns.End(xlUp).Offset(1, 0) End With With ListBox1 If .ListIndex -1 Then destCell.Value = .List(.ListIndex) End If End With Unload Me messagebox.Show End Sub Thanks again Greg -- Dave Peterson |
#4
|
|||
|
|||
Whoops. I didn't notice that the message was cross posted.
Dave Peterson wrote: You've got a reply at one of your other posts. Greg B wrote: Hi all Dave Peterson helped me by writing this code but I need it to do a little more and I cant seemto work it out. What the code below does isshows the meeting that are coming up and copies the information to another worksheet which I will use another way. But at the moment it only copies the first cell. The list's rowsource is a2:e65536 so I probably need to have the code copy the whole line. Any ideas would be appreciated. Here is the code Private Sub CommandButton1_Click() Dim destCell As Range With Worksheets("Popup") Set destCell = .Columns.End(xlUp).Offset(1, 0) End With With ListBox1 If .ListIndex -1 Then destCell.Value = .List(.ListIndex) End If End With Unload Me messagebox.Show End Sub Thanks again Greg -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |