Consulting

Results 1 to 10 of 10

Thread: find the range in a sheet

  1. #1
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location

    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. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    [vba]

    Dim cell As Range

    Set cell = Worksheets("Sheet2").Cells.Find(Me.TextBox1.Text)
    If Not cell Is Nothing
    ...
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location
    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. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    [vba]

    RowNum = Application.Match,(TextBox1.Text, Columns(1),0)
    TextBox2.Text = Cells(i, "B").Value + Cells(i, "C").Value - Cells(i, "D").Value
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location
    it didn't work
    it gave a compiled error on the "i"

  6. #6
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location
    I tried to add some Dims, still

    'Dim rowNum As Variant
    'Dim i As Integer

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location
    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

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,222
    Location
    Just add it then

    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Regular
    Joined
    Oct 2007
    Posts
    97
    Location
    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.

    You were very helpful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •