Userform Listbox, adding new column by multiplying selected rows with a specific column by a textbox value.

[Tades101]

Hi Everyone

I have a listbox as such:

I want to be able to multiply the Weight (kg) column with my selected rows only by the value in the text box seen above the Qty button. I then want to add those values to the listbox as a new column.

I have no idea on how to do this I have tried a few things to no avail.

Any help would be much appreciated

thanks!

 

[DanteAmor]

Hi and welcome to MrExcel!

I have a listbox as such:

In your image I can see some data loaded in your listbox, you can put here the code or what you have used to load that data in the listbox.
Based on that, I will explain what you must do to add the third column.

 

[Tades101]

Hi yes, the data is loaded in the listbox via code in the userform
I have it almost working....
I am just at the moment trying to select the rows and paste the it into the selected column like this:

Private Sub timestab_Click()
n = 0
For i = 0 To Me.lstMulti.ListCount - 1
If Me.lstMulti.Selected(i) = True Then
n = n + 1
Sheet2.Range("F2" & n) = Me.lstMulti.List(i, 1)
End If
Next

End Sub


However it does not start my at F2 and list it down, it starts from like F21 for some reason?

 

[DanteAmor]

the data is loaded in the listbox via code in the userform

I tried to be as specific as possible for you to put that code here, but you put me another code.
I need to see the code that you use to load the data inside the userform.
Can you put that code here please.


What is the end goal, do you want to add a third column in the listbox or do you want to put data in the sheet?

I want to be able to multiply the Weight (kg) column with my selected rows only by the value in the text box seen above the Qty button. I then want to add those values to the listbox as a new column.

Where do you want that column in the listbox or in the sheet?

And don't forget to put the code here.

Also, if you can, give an example of how the data is on the sheet.

 

[Tades101]

oh very sorry
The listbox is using a Name Manager
which is called CL1_
The formula for that is:
=OFFSET(Sheet3!$J$207,1,0,COUNTA(Sheet3!$J$208:$K$273),50)
That is what populates the listbox

I would like that 3rd column to be in another sheet, not the listbox

So selected rows in the list box, have their second column (weight) multiplied by the value in the text box when I hit the button and then pasted into another sheet, in this case starting in column F2. However I don't want gaps when it pastes into the column, I would like it to be from F2,F3,F4 etc depending on how many rows I select.

Thank you

 

[DanteAmor]

Your code is fine, the problem is in these lines:

n = n + 1
Sheet2.Range("F2" & n) = Me.lstMulti.List(i, 1)

I explained to you

In the first cycle n is equal to 1
So "F2" & n
Then "F2" & 1
Then "F21"
So start typing in cell "F21"

It should be like this:

Private Sub timestab_Click()
n = 1
For i = 0 To Me.lstMulti.ListCount - 1
If Me.lstMulti.Selected(i) = True Then
n = n + 1
Sheet2.Range("F" & n) = Me.lstMulti.List(i, 1) * textbox1.value
End If
Next

End Sub

Note: fit textbox1 with the nane of your textbox.

 

[Tades101]

Thank you so much SIR!!! big help!

Your code is fine, the problem is in these lines:

n = n + 1
Sheet2.Range("F2" & n) = Me.lstMulti.List(i, 1)

I explained to you

In the first cycle n is equal to 1
So "F2" & n
Then "F2" & 1
Then "F21"
So start typing in cell "F21"

It should be like this:

Private Sub timestab_Click()
n = 1
For i = 0 To Me.lstMulti.ListCount - 1
If Me.lstMulti.Selected(i) = True Then
n = n + 1
Sheet2.Range("F" & n) = Me.lstMulti.List(i, 1) * textbox1.value
End If
Next

End Sub

Note: fit textbox1 with the nane of your textbox.

ha

 

[Tades101]

May I please ask one more question, if my listbox contains rows with no value in the (i.e. numbers) in the second column I would like it to still return the column 1 row and just have the second column which the script above is multiplying as blank. However it gives me a run time error 13.

How would I write that code please?

Many thanks