userform/textbox question

[viper]

Suggestions are very welcome with this. I have a workbook I'm making, in column A there will be a number 1-85 then in column D will be a figure that will match that number, I will be using a userform to input the data from an invoice. What I need to know is, instead of using 85 IF statements how else would I get the information from the text boxes to the correct cells? I have one text box that the user will enter a number from 1-85 and another text box where they will enter a figure.

Better explination: In cells A10:A95 there is a number from 1-85, if the user enters say 45 in the first textbox, what other way can I use so that when the okay button is clicked the 45 will end up in cell A55 and the figure in the second textbox will end up in D55? I'm thinking maybe a Index/Match or VLookup but I'm not very familiar with either of those.

Thanks,

Sorry, I only need the figure to be posted into cell D55 if the first textbox contains the number 45.
_________________
Soon I hope to be answering alot of these questions instead of asking them.

viper

[ This Message was edited by: viper on 2002-05-11 23:00 ]

[Tom Schreiner]

Not tested Viper...

Sub CommandOKAY_Click()

'using textbox2 for value to be placed in column D

'using textbox1 for number user entered

'assuming activesheet is the correct sheet to place

'the value



'check for a valid number

If Not IsNumeric(textbox1) Then

    MsgBox "Need Number"

    textbox1.SetFocus

    Exit Sub

End If

If textbox1.Value > 0 And _

   textbox1.Value < 86 Then

    ActiveSheet.Cells(textbox1.Value + 10, 1).Value = _

    textbox1.Value

    ActiveSheet.Cells(textbox1.Value + 10, 4).Value = _

    textbox2.Text

Else

    'if number is out of range

    MsgBox "Range 1 to 85"

    textbox1.SetFocus

    Exit Sub

End If

End Sub

This would actually make your placement range A11:A95 not A10:A95...

Tom



[ This Message was edited by: TsTom on 2002-05-11 23:13 ]

[datastream]

One way you could do it, the number from the first textbox could be inserted into a selection, and then write in column D in the corresponding row selection. So you basically select row of whatever number is entered in the first text box.

[viper]

Tom,

Formula worked great, Thank you.