Run Time 13 Mismatch Error

[Tades101]

Hi Everyone

I have a listbox in a user form that is populated using a dynamic array in a name manager which it is referencing.

The listbox has 2 columns.

First column is part number, second column is it's weight.

Now some part numbers do not have weights i.e. second column is empty.

I am trying to multiply the selected rows by a textbox value which I am then copying into another sheet using a command button.

However, if the text box is empty it won't run "Run Time 13 Error"

It also doesn't run when I try to multiply rows with empty second column values by the textbox value (if I input one in).

Basically I would like it to still copy over the first column via the command button regardless of when the second column has a value or the text box has a value. Preferably I would like the second column to be just Null if this is the case.

Can you please let me knowhow to do this Below is my code?

Private Sub cmdAdd_Click()
Dim addme As Range, cNum As Integer
Dim x As Integer, y As Integer, Ck As Integer

'set variables
Set addme = Sheet2.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
cNum = 3
Ck = 0
'run the for loop
For x = 0 To Me.lstMulti.ListCount - 1
'add condition statement

If Me.lstMulti.Selected(x) Then
Ck = 1
'second loop
For y = 0 To cNum
addme.Offset(0, y) = Me.lstMulti.List(x, y)


Next y
Sheet2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = Me.lstMulti.List(x, 1) * TextBox1.Value
Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = TextBox1.Value
Set addme = addme.Offset(1, 0)
End If

'clear the selected values
lstMulti.Selected(x) = False
Next x
'send a message if nothing is selected
If Ck = 0 Then
MsgBox "There is nothing selected"
End If


End Sub

 

[Alex Blakenburg]

I assume your error is on the line below:

Sheet2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = Me.lstMulti.List(x, 1) * TextBox1.Value

How about replacing it with this:

If Me.lstMulti.List(x, 1) <> "" And TextBox1.Value <> "" Then
    Sheet2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = Me.lstMulti.List(x, 1) * TextBox1.Value
Else
    Sheet2.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = Empty
End If

If you know the cell is going to be empty to start with you won't need the Else part.

 

[Tades101]

Thank you
I just have one issue now,
If I choose 1 row that has a blank second column and then a second row that does have a value and put in 1 in the textbox
It doesn't put the blank value in the correct spot, it puts it in the spot where the row had a second column value and the second column value in the row that was blank?

I think its some sort of offset issue with my code, can you please have a look?

THank you!

 

[Alex Blakenburg]

Can you provide an image of what it is doing and what you would like it to do ?
Also which line of code you think is the issue ?

 

[Tades101]

Hi

So when I use my user for and select the following for example and then enter 2 into my textbox

and hit the "add weight" button to copy over to my sheet it gives me this

But I would like it to be this

The empty cells don't seem to be lining up with the correct part numbers

thanks heaps!

 

[Tades101]

I believe its with the offset lines, I am not too sure

 

[Alex Blakenburg]

It is because you are using a different column to work out the next row to use for 3 different output lines.
eg addme you are using column 4, then the Qty you are using column 3 and the result you are using column 6.
Since column 6 has more blank rows the End(xlUp) is going to a much higher row than doing the same think on Column 3

I don't follow all your code but if addme is the reference point then you could try something like.

Sheet2.Cells(addme.row, 6) = Me.lstMulti.List(x, 1) * TextBox1.Value
Sheet2.Cells(addme.row, 3) = TextBox1.Value

 

[Tades101]

worked well thanks soo much!

 

[Alex Blakenburg]

You're welcome. Glad I could help.