CODEWITHSUNDEEP
excelvbastringnumbersuserform
user2566496
user2566496

Reputation: 11

How to get only number from .address VBA Excel

I'm trying to code a userform in which if a cmdbuton is pressed the userform will evaluate in which of the two textboxes the data is and look at the position (first textobx) or find the name of the data (second textbox).

I'm having problems to analyze the name of the textbox. I would like to find the name, get its location and load in another userform all the data from that row. The problem is I don't know how to take away the number from the .address that I get when I find the word.

Private Sub CommandButton2_Click()
If Not TextBox1.value = "" And TextBox2.value = "" Then
    Sheet1.Cells(1, 15) = TextBox1.value
    Unload Me 
    OpisVentila.Show
ElseIf TextBox1.value = "" And Not TextBox2.value = "" Then
Dim y As Range
Dim x As String
Dim z As String
Dim s As String
    x = TextBox2.Text
    Set y = Sheet1.Range("A:A").Find(x)
    z = y.Address(0, 0)
    s = StrConv(z, vbUnicode)
    s = Split(Left(s, Len(s) - 1, vbNullChar))
    Sheet1.Cells(1, 15) = s
    Unload Me 
    PromjenaVentila.Show
ElseIf Not TextBox1.value = "" And Not TextBox2.value = "" Then
    Msgbox "Please enter one way to find the desired location"
    TextBox1 = ""
    TextBox2 = ""
Else
    Msgbox "You didn't chose any way to find the desired location"
End If
End Sub

Upvotes: 1

Views: 1298

Answers (1)

ACCtionMan
ACCtionMan

Reputation: 511

The following code will give you the row number:

Dim y as Range
Dim z As Integer
Set y = Sheet1.Range("A:A").Find(x)
z = y.Row

Upvotes: 2

Related Questions