PDA

View Full Version : find the range in a sheet



fadib
11-20-2007, 11:05 AM
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.

:cool:

xld
11-20-2007, 11:24 AM
Dim cell As Range

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

fadib
11-20-2007, 12:26 PM
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.

xld
11-20-2007, 12:41 PM
RowNum = Application.Match,(TextBox1.Text, Columns(1),0)
TextBox2.Text = Cells(i, "B").Value + Cells(i, "C").Value - Cells(i, "D").Value

fadib
11-20-2007, 01:28 PM
it didn't work :(
it gave a compiled error on the "i"

fadib
11-20-2007, 01:41 PM
I tried to add some Dims, still :(

'Dim rowNum As Variant
'Dim i As Integer

xld
11-20-2007, 01:54 PM
Didn't mean i



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

fadib
11-20-2007, 02:23 PM
Xld I am sure your code works, maybe I am not putting it in the rightway.
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

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

xld
11-20-2007, 02:35 PM
Just add it then



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

fadib
11-20-2007, 02:42 PM
XLD :bow: 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.

You were very helpful :clap: