Label string to extract numbers from a table

[ana1991]

Hi all

I'm having a problem in extracting coordinates from a table after writting the name on a label.
Can anyone please help on this?

Thanks in advance!
Ana


My code is currently the following:
(N1 and N2 are the coordinates to extract from the table considering the words on userform1.label1.caption)

Sheets("sheet1").Select
Range("A1").Select
ncategories = WorksheetFunction.CountA(Columns("A:A"))
For i = 1 To ncategories
If Range("A1:A" & ncategories).Cells(i, 1) = UserForm1.Label1.Caption Then
Range("A" & i).Select
N1 = ActiveCell.Offset(UserForm1.Label11.Caption, 1)
N2 = ActiveCell.Offset(UserForm1.Label12.Caption, 2)
End If
Next i

 

[Fluff]

What are the captions of Label11 & Label12?
Also what are you trying to store in N1 & N2?

 

[ana1991]

What are the captions of Label11 & Label12?
Also what are you trying to store in N1 & N2?

Hi Fluff

Sorry there is a mistake, Label12 should be Label11 as well, so N2 on the code above should be:
N2 = ActiveCell.Offset(UserForm1.Label11.Caption, 2)

Label1 is the surmane of a person. Label11 is the first name of a person with a surmane of Label1.caption.

N1 & N2 are the adress (N1) and zip-code (N2) of the person with first name of "Label11.caption" and surname "Label1.caption"


Thanks a lot

 

[Fluff]

If column A has the surnames, which column has the first name?

 

[ana1991]

If column A has the surnames, which column has the first name?

Column D

 

[Fluff]

Forgot to ask, which columns are address & Zip code?

 

[ana1991]

Forgot to ask, which columns are address & Zip code?

Surname: column A
Adress: column B
Zip code: column C
First name: column D

 

[Fluff]

Ok, how about

   Dim Cl As Range
   
   With Sheets("sheet1")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Cl.Value = UserForm1.Label1.Caption And Cl.Offset(, 3).Value = UserForm1.Label11.Caption Then
            N1 = Cl.Offset(, 1).Value
            N2 = Cl.Offset(, 2).Value
            Exit For
         End If
      Next Cl
   End With

 

[ana1991]

Ok, how about

   Dim Cl As Range
  
   With Sheets("sheet1")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Cl.Value = UserForm1.Label1.Caption And Cl.Offset(, 3).Value = UserForm1.Label11.Caption Then
            N1 = Cl.Offset(, 1).Value
            N2 = Cl.Offset(, 2).Value
            Exit For
         End If
      Next Cl
   End With

It's perfect, thanks a lot Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.