Is there a way to do the following?

[viper]

When a user clicks a command button how would I write code that will check the number the user entered into a textbox to the same number in cells A8:A93 and then insert the value the user entered into another textbox in column D of that row?

TsTom, First thanks for all your help so far. I'm trying a different way to get what you've been helping me with. It's just to hard to explain what I'm trying to do with out you seeing my workbook. So, I figure if I can compare the two numbers then I can hopefully get it to do the other part as well.

thanks,

[Tom Schreiner]

Hi Vip.
I gotta run...
Textbox1 is the number to search
Textbox2's value or text will be placed
in Column D on the same row...

Private Sub CommandButton1_Click()

Dim c

For Each c In Range("A8:A93")

Debug.Print c.Value

    If c.Value = Val(TextBox1) Then

        Cells(c.Row, 4) = TextBox2

        Exit For

    End If

Next

End Sub

Tom

[Colo]

Hi viper,

If you need to show the value of textbox2,
please try this.

Private Sub TextBox1_Change()

Dim ret

ret = Application.Match(Val(TextBox1.Text), [A8:A93]), 0)

If Not IsError(ret) Then TextBox2.Text = [A9].Cells(ret - 1, 4).Value

End Sub

_________________
With regards,
Colo

[ This Message was edited by: Colo on 2002-05-14 23:35 ]

[viper]

Thank you TsTom worked great, now for the next part.

For Each c In Range("A8:A93")
Debug.Print c.Value
If c.Value = Val(TextBox1) Then
Cells(c.Row, 4) = TextBox4
Exit For
End If
Next
Now I need to check that cell "Cells(c.Row,4) = TextBox4" if it is not blank then I need to insert a row and enter the data into the new row 4th column. But only if the cell is not blank, if it is blank then insert the data into the cell. Then after that I need to have the Range("A8:A93") increased by one to be Range("A8:A93").

What I am trying to accomplish is this.
I have the cells A8:A93 that contain a number 1-85 and the cell in column d will contain a figure for that number. When the user enters a number into the textbox1 (1-85) and then enters a figure into textbox4 the figure will be put into column d that matches the number the user entered into textbox1. But if the user enters the same number in textbox1 twice or more I need for a new row to be inserted so that it will keep more than one entry for each number (1-85). Right now all I can do is overwrite the data each time.
If I enter number 1 into textbox1 and 15.00 into textbox4, the code puts 15.00 into row 8 column D because the number 1 is in Row 8 column A. But if I enter 1 again and 25.00 in the other textbox the code will overwrite the 15.00 with the 25.00, I need for it to be put under the 15.00 hence the inserting of a new row.

If I am doing the hard way please someone slap me with an easier way.

thanks,

[viper]

Thanks Colo,

[Tom Schreiner]

Hey Viper.
I don't know what you are doing, so I can't tell if there is a better way...
For the time being, name your range so that when rows are added, the named range will automatically expand...

In this ex. your named range for A8:A93 is "YourRange"

Private Sub CommandButton1_Click()

Dim c

For Each c In Range("YourRange")

Debug.Print c.Value

    If c.Value = Val(TextBox1) Then

        If Cells(c.Row, 4) <> "" Then

            Cells(c.Row + 1, 4).Rows.EntireRow.Insert

            Cells(c.Row + 1, 4) = TextBox2

        Else

            Cells(c.Row, 4) = TextBox2

        End If

        Exit For

    End If

Next

End Sub

Tom