Insert Listbox Content on Double Click

[adamsm]

Hi anyone,

I'm using the following code to insert the list box content to the cells "I25:I33" and cells "K25:K33".

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim r As Long
  With Worksheets("MySheet")
    If Range("I25") = "" Then
      r = 25
    Else
      r = .Range("I24").End(xlDown).Row + 1
    End If
    .Range("I" & r) = Me.ListBox1.Column(1)
  End With
End Sub

But the code inserts the listbox contents from I25 and onwards until the last row of the excel sheet.

How can I modify the code, so that when the user double clicks the list box; the double clicked row contents get copied to rows starting from I25:I33 and then to K25:K33.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Domenic]

Somewhat unclear... If, for example, you'd like to copy the double-clicked row to the first available row in Column I, starting at I25, try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ListBox1_DblClick([color=darkblue]ByVal[/color] Cancel [color=darkblue]As[/color] MSForms.ReturnBoolean)
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]With[/color] Worksheets("MySheet")
        LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
        [color=darkblue]If[/color] LastRow >= 25 [color=darkblue]Then[/color]
            r = LastRow + 1
        [color=darkblue]Else[/color]
            r = 25
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        .Cells(r, "I").Value = Me.ListBox1.Column(1)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[adamsm]

Thanks for the help and I do really appreciate it.