# Thread: find the range in a sheet

1. ## find the range in a sheet

Hi guys,
I need some help in the following:
I have data in sheet2.
the user will input a name in a textbox.
I want to find the range in sheet2 in which the input resides.

I hope you guys understand my complicated descritption.

2. [vba]

Dim cell As Range

Set cell = Worksheets("Sheet2").Cells.Find(Me.TextBox1.Text)
If Not cell Is Nothing
...
[/vba]

3. OKay, I think I have formulated my question wrong:
This is my scenario:
I have two textbox: Textbox1 and texbox2
I also have data stored in sheet2:
example:
A B C D
1 P1 10 2 2.25
2 P2 20 2 2.25
3 P3 30 2 2.25
4 P4 40 2 2.25
5 P5 50 2 2.25

The user will input a code from the second column in textbox1.
Example: P1

Then the VBA code will have to find P1, identify its range.
P1 for example is in A1

Then after identifying the row and column of P1, I want the VBA code to Display in textbox2 the sum of B1 + C1 - D1 or 10 +2 - 2.25 = 9.75

The data that I have in sheet 2 should not change, values should only be used.

4. [vba]

RowNum = Application.Match,(TextBox1.Text, Columns(1),0)
TextBox2.Text = Cells(i, "B").Value + Cells(i, "C").Value - Cells(i, "D").Value
[/vba]

5. it didn't work
it gave a compiled error on the "i"

6. I tried to add some Dims, still

'Dim rowNum As Variant
'Dim i As Integer

7. Didn't mean i

[vba]

RowNum = Application.Match(TextBox1.Text, Columns(1), 0)
TextBox2.Text = Cells(RowNum, "B").Value + Cells(RowNum, "C").Value - Cells(RowNum, "D").Value
[/vba]

8. Xld I am sure your code works, maybe I am not putting it in the rightway.
[vba]Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowNum As Variant
RowNum = Application.Match(TextBox1.Text, Columns(1), 0)
TextBox2.Text = Cells(RowNum, "B").Value + Cells(RowNum, "C").Value - Cells(RowNum, "D").Value
End Sub[/vba]

I am looking at the code I don't see any reference to sheet2.
Is there a way to display in what cell we are? Like in the previous example if P1 is entered that means we are in fcell A1

[vba]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowNum As Variant
With Worksheets("Sheet2")
RowNum = Application.Match(TextBox1.Text, .Columns(1), 0)
TextBox2.Text = .Cells(RowNum, "B").Value + .Cells(RowNum, "C").Value - .Cells(RowNum, "D").Value
End With
End Sub
[/vba]

10. XLD you are gooooodd!!!!

Thank you so much for your patience, I am still new in VBA for excel and I am tryng to understand the language.