Use listbox to populate ranges

[enthomio]

Hi, I have a couple of issues.

First, I am trying to use a listbox to insert values into specific rows in a range.

Here is the code to populate the listbox, which seems to be working fine:

Private Sub CommandButton1_Click()
Dim LR As Long
Dim r As Long
Dim i As Long
ListBox1.Clear
With ThisWorkbook.Sheets("FormValues")
LR = .Range("A" & .Rows.Count).End(xlUp).row


' (r1 has header data)
For r = 2 To LR

' (columns 1 and 2 should always be populated as reference ...year and age)
ListBox1.AddItem .Cells(r, 1).Value
ListBox1.List(i, 1) = .Cells(r, 1).Value
ListBox1.AddItem .Cells(r, 2).Value
ListBox1.List(i, 2) = .Cells(r, 2).Value

If .Cells(r, 1).Value >= Val(Start.Value) And .Cells(r, 1).Value < Val(Start.Value) + Val(DF_End) Then
' (columns 3 and 4 are only populated when they meet the year criteria) ListBox1.AddItem .Cells(r, 3).Value
ListBox1.List(i, 3) = .Cells(r, 3).Value
ListBox1.AddItem .Cells(r, 4)
ListBox1.List(i, 4) = .Cells(r, 4)


Else
' ListBox1.AddItem .Cells(r, 3).Value
ListBox1.List(i, 3) = ""
' ListBox1.AddItem .Cells(r, 4)
ListBox1.List(i, 4) = ""
End If
i = i + 1
Next r
End With


End Sub

What this does a use a range I set in two fields within the userform to determine which items in the listbox are populated (columns 1 and 2 are always populated).

It's when I try to transfer the values back to the worksheet that I am having trouble:

Private Sub CommandButton2_Click()
Dim addme As Range
Dim x As Integer
Set addme = Sheets("income1").Cells(Rows.Count, 12).End(xlUp)
For x = 0 To Me.ListBox1.ListCount - 1
addme.Offset(0, 0) = Me.ListBox1.List(x, 3)
Set addme = addme.Offset(1, 0)
Unload Me
End Sub

It works fine as long as none of the cells in the worksheet contain any values. If they do, it deletes the last value and then starts the count from there. For example if I run this code on the empty column using a range of 5, 5 (beginning at row 5, column 12 and ending at r 9, c 12) and then run it again for 10, 5 (beginning at r 10, c 12 and ending at r14, c12), it will delete the value in r 9, c 12 and start inserting data at r 13 and end at r 17.

Finally, I can get this to work fine when I am inserting string values into the listbox. I would really like to pull formulas from the worksheet and insert the formulas into the cells. I have no problem loading them into the listbox by substituting .formula for .value in the first routine, but I have not been able to figure out how to get the formula back into the rows on the worksheet.

Any ideas? Thanks in advance.

Steve

 

[enthomio]

I can't figure out how to edit this...noticed a couple of errors, so I guess I will just do the reply thing. If anyone can help me out on this as well I would appreciate it very much.

Hi, I have a couple of issues.

First, I am trying to use a listbox to insert values into specific rows in a range.

Here is the code to populate the listbox, which seems to be working fine:

Private Sub CommandButton1_Click()
Dim LR As Long
Dim r As Long
Dim i As Long
ListBox1.Clear
With ThisWorkbook.Sheets("FormValues")
LR = .Range("A" & .Rows.Count).End(xlUp).row


' (r1 has header data)
For r = 2 To LR

' (columns 1 and 2 should always be populated as reference ...year and age)
ListBox1.AddItem .Cells(r, 1).Value
ListBox1.List(i, 1) = .Cells(r, 1).Value
ListBox1.AddItem .Cells(r, 2).Value
ListBox1.List(i, 2) = .Cells(r, 2).Value

If .Cells(r, 1).Value >= Val(Start.Value) And .Cells(r, 1).Value < Val(Start.Value) + Val(DF_End) Then
' (columns 3 and 4 are only populated when they meet the year criteria)
ListBox1.AddItem .Cells(r, 3).Value
ListBox1.List(i, 3) = .Cells(r, 3).Value
ListBox1.AddItem .Cells(r, 4)
ListBox1.List(i, 4) = .Cells(r, 4)


Else
' ListBox1.AddItem .Cells(r, 3).Value
ListBox1.List(i, 3) = ""
' ListBox1.AddItem .Cells(r, 4)
ListBox1.List(i, 4) = ""
End If
i = i + 1
Next r
End With


End Sub

What this does a use a range I set in two fields within the userform to determine which items in the listbox are populated (columns 1 and 2 are always populated).

It's when I try to transfer the values back to the worksheet that I am having trouble:

Private Sub CommandButton2_Click()
Dim addme As Range
Dim x As Integer
Set addme = Sheets("income1").Cells(Rows.Count, 12).End(xlUp)
For x = 0 To Me.ListBox1.ListCount - 1
addme.Offset(0, 0) = Me.ListBox1.List(x, 3)
Set addme = addme.Offset(1, 0)
Unload Me
End Sub

It works fine as long as none of the cells in the worksheet contain any values. If they do, it deletes the last value and then starts the count from there. For example if I run this code on the empty column using a range of 5, 5 (beginning at row 5, column 12 and ending at r 9, c 12) and then run it again for 10, 5 (beginning at r 10, c 12 and ending at r14, c12), it will delete the value in r 9, c 12 and start inserting data at r 13 and end at r 17.

Finally, I would really like to pull formulas from the worksheet and insert the formulas into the cells. I have no problem loading them into the listbox by substituting .formula for .value in the first routine, but I have not been able to figure out how to get the formula back into the rows on the worksheet.

Any ideas? Thanks in advance.

Steve