still can't get this right

[olorin]

I have a userform that has a listbox and a textbox in it and a couple of buttons.
The listbox is populated by a named range in a different sheet than the main sheet where all the userforms open.
I need the value of the textbox ("productionfigure") to be entered into a particular row in the production worksheet dependant upon what was selected in the listbox.
I have this code that works fine (except for gettin it on the right sheet), but the listbox has about 120 items and I really don't want to enter 120 "If" statements.
I have been helped so much on here (hence gettin even this far), and this is the code that works if i want to add 120 "if" statements

Private Sub ProdFigOK_Click()
If ListBox1.Value = "640001A" Then
row = 2
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
If ListBox1.Value = "641001A" Then
row = 3
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
productionfigure.Value = 0
End Sub

P.S. The last line is to set the value of the textbox to "0", is there a command that would clear the contents after the calculation instead of setting it to zero?
Your help is as always greatly appreciated.

 

[anthonya2369]

What sheet do you want it on? Set the textbox to a clear value, you just select .text instead of .value and set it to "".

ie.

productionfigure.text = ""

to put the value to another sheet, you will need to specify what sheet you need the value to go to. Does 640001A represent the sheet name?

 

[olorin]

no anthony, the destination sheet name is "Production", and thanks for the simple solution to textbox clearing.
The main thing is to try and do away with the 120 "If" statements if possible.
Thanx for ur reply

 

[olorin]

Maybe this will help.

Listbox1 is populated from a named list in the sheet "Production".
When the user clicks the relevant product and then "OK" I wish the result to be inserted into the next empty cell in the same row as the product name.
Any help would be appreciated.

 

[anthonya2369]

A way around the 120 if statement solution CAN be simple. I noticed that you said if it is the first one, it goes in b2, the next one, b3 and so on. You can use the listbox1.listindex to help you put it in the right column and row. The column seems to stay in column 2 (B). So you could right code like this:

cells(listbox1.listindex + 1, 2).value = productionfigure.value

See if that helps instead of using all those if statements.

 

[olorin]

the column would change each time i added a number to the particular product. but the row would remain the same.
so would that mean i could use
LastCol = Sheets("production").Range("IV").End(xlToLeft).Column
Cells(listbox1.listindex +1, LastCol + 1) = productionfigure.Value
Or am I still missing the point?

 

[olorin]

just tried what I wrote previously.......doesn't work
but what you wrote anthony works fine on the active sheet but doesn't enter the result into the "production" sheet or into the next available column. it writes it into comlumn 2 all the time.
How can I change
cells(listbox1.listindex + 1, 2).value = productionfigure.value
to write the result into the next empty column of the relevant row on the "production" sheet?

 

[anthonya2369]

findrow = listbox1.ListIndex + 1

Sheets("production").Select

Cells(findrow, 1).EntireRow.End(xlToRight).Select

With Selection
    findcol = .Column
    findrow = .Row
End With

findcol = findcol + 1

Cells(findrow, findcol).Value = productionfigure.Value

Try that and let me know.

 

[olorin]

thanx anthony, it works a treat as far as putting the data into the production sheet on the correct row, but i keeps overwriting the value in column 2 instead of entering new entries in the next available column in the row.
Thanx for ur time