Solved: need help urgently

[olorin]

http://users.igl.net/toonie/userfrm1.jpg
The UserForm above opens on sheet1 of my workbook.
I can get it to enter the productionfigure value into sheet1 into the relevant row dependant on what was clicked in ListBox1.
The problem is that I need the value to be entered into a different sheet named "Production".
ListBox1 is populated by a named range which at present has 131 items in it, and the macro calculates which row to put productionfigure.value into with 131 "If....Then" statements !!!!
Which is not only untidy but tiresome when a new item is added.
I need the productionfigure.value to be entered into the NEXT EMPTY cell in the relevant row depending on what was selected in ListBox1 in the Production sheet.
for example; if item 3 is selected i wish productionfigure.value to be entered into the next empty cell in row 3 of Production sheet, and if item 12 is selected then into the next empty cell in row 12 of Production sheet.
Also it would be nice if there is a smarter way to decide what was selected from ListBox1 without a zillion "If...Then" statements.
Your time is much appreciated.

[Norie]

You can get the value of a listbox like this.



Msgbox ListBox1.Value



To get the relevant row.



MsgBox Listbox1.ListIndex + 1



You need to add 1 because the listindex is 0 based.

To find the next empty cell in a row.



MyRow = Listbox1.ListIndex+1

NextCol = Range("IV" & MyRow).End(xlToLeft).Column

[olorin]

OK Thanx for that.
I have done this.

Private Sub ProdFigOK_Click()
findrow = ListBox1.ListIndex + 1
Sheets("production").Select
Cells(findrow, 1).Range("IV" & findrow).End(xlToLeft).Select
With Selection
findcol = .Column
findrow = .row
End With
findcol = findcol + 1
Cells(findrow, findcol).Value = productionfigure.Value
productionfigure.Value = "" (this line to clear value after entry)
End Sub


But it seems to miss rows, I seem to be missing something.
Thanx for ur time

[olorin]

I have also tried this to no avail,

Private Sub ProdFigOK_Click()
findrow = ListBox1.ListIndex + 1
Sheets("production").Select
NextCol = Range("IV" & findrow).End(xlToLeft).Column
With Selection
findcol = .Column
findrow = .row
End With
findcol = findcol + 1
Cells(findrow, findcol + 1).Value = productionfigure.Value
productionfigure.Value = ""
End Sub

[johnske]

Is this what you're trying to do?Private Sub ProdFigOK_Click()
Sheets("production").Range("IV" & ListBox1.ListIndex + 1) _
.End(xlToLeft).offset(0, 1) = ListBox1
End SubI don't know what 'productionfigure' is, if it's a Named Range I suggest you use productionfigure.ClearContents as it's much faster than productionfigure.Value = ""

HTH,
John :)

[olorin]

absolutely fandabbybloodydocious !!!!!!
exactly what I was trying to achieve !
Brilliant!
Thank you (131 If... Then) times :clap:

[olorin]

As a footnote;
After studying the code, I have learnt so much more than just the solving of this particular problem. It will make my Excel life much easier.
Thank you so much.

p.p.s. "productionfigure" is the name of the input box, and I tried the "clearcontents" thingy and it came up with an error, so i have the input box clear with ....Value =""
Thanx again