Is it possible...lookup macro
 

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

Is it possible...lookup macro
 

Thank you very much. I knew there had to be a cleaner way to do what I
wanted, a way to do it all in a macro. You have answered a couple of my
questions several years ago and they have always been exactly what I was
looking for, again thank you!

"Tom Ogilvy" wrote:

Dim r as Range, r1 as Range
With Worksheets("Data")
set r = .Range(.cells(3,19),.cells(1000,19))
set r1 = .Range(.cells(3,1),.cells(1000,1))

End With

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),r,r1)

--
Regards,
Tom Ogilvy


"Haxer" wrote:

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

Is it possible...lookup macro
 

I hate to ask this after just saying that it is what I was looking for but
now I'm not sure. It looked like it was exactly what I wanted but when I
actually put the code in place and try to run it VBA doesn't recognize
lookup. I looked at the object browser and couldn't find it either. Is there
something else that should be there instead of lookup? Thanks for your time.

"Tom Ogilvy" wrote:

Dim r as Range, r1 as Range
With Worksheets("Data")
set r = .Range(.cells(3,19),.cells(1000,19))
set r1 = .Range(.cells(3,1),.cells(1000,1))

End With

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),r,r1)

--
Regards,
Tom Ogilvy


"Haxer" wrote:

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

Is it possible...lookup macro
 

I figured out a way to make it work...

Dim i As Long
Dim iLastRow As Long
With Sheets("Data")
iLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
For i = 1 To iLastRow Step 1
If .Cells(i, "S").Value = S1.Value Then
UserFormview.V1.Value = .Cells(i, "A").Value
End If
Next i
End With

S1.Value = ""

I can't find any problems with it. If you do know of an easier way I would
love to learn it. I'm going to need to use this code alot in the workbook I'm
working on. Thank you for your time.

"Tom Ogilvy" wrote:

Dim r as Range, r1 as Range
With Worksheets("Data")
set r = .Range(.cells(3,19),.cells(1000,19))
set r1 = .Range(.cells(3,1),.cells(1000,1))

End With

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),r,r1)

--
Regards,
Tom Ogilvy


"Haxer" wrote:

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

Is it possible...lookup macro
 

I didn't look at that line closely i was focused on showing how to do the
range arguments.

UserFormview.V1.Value =
Application.LOOKUP(UserFormSearch.S1.Value,r,r1)

You will have to check the syntax of the worksheetfunction LOOKUP and make
sure you arguments conform to that. Since you are looping 1000 rows, it
might be better to use the Lookup which should be much faster or use the VBA
FIND method of the Range object.

--
Regards,
Tom Ogilvy




"Haxer" wrote:

I figured out a way to make it work...

Dim i As Long
Dim iLastRow As Long
With Sheets("Data")
iLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
For i = 1 To iLastRow Step 1
If .Cells(i, "S").Value = S1.Value Then
UserFormview.V1.Value = .Cells(i, "A").Value
End If
Next i
End With

S1.Value = ""

I can't find any problems with it. If you do know of an easier way I would
love to learn it. I'm going to need to use this code alot in the workbook I'm
working on. Thank you for your time.

"Tom Ogilvy" wrote:

Dim r as Range, r1 as Range
With Worksheets("Data")
set r = .Range(.cells(3,19),.cells(1000,19))
set r1 = .Range(.cells(3,1),.cells(1000,1))

End With

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),r,r1)

--
Regards,
Tom Ogilvy


"Haxer" wrote:

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

Is it possible...lookup macro
 

Thanks very much! This will save me a lot of time. The way I figured out does
work but to do it for many different arguments takes quite a while. Thanks,
hopefully someday I will be able to answer someones questions instead of
always asking.

"Tom Ogilvy" wrote:

I didn't look at that line closely i was focused on showing how to do the
range arguments.

UserFormview.V1.Value =
Application.LOOKUP(UserFormSearch.S1.Value,r,r1)

You will have to check the syntax of the worksheetfunction LOOKUP and make
sure you arguments conform to that. Since you are looping 1000 rows, it
might be better to use the Lookup which should be much faster or use the VBA
FIND method of the Range object.

--
Regards,
Tom Ogilvy




"Haxer" wrote:

I figured out a way to make it work...

Dim i As Long
Dim iLastRow As Long
With Sheets("Data")
iLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
For i = 1 To iLastRow Step 1
If .Cells(i, "S").Value = S1.Value Then
UserFormview.V1.Value = .Cells(i, "A").Value
End If
Next i
End With

S1.Value = ""

I can't find any problems with it. If you do know of an easier way I would
love to learn it. I'm going to need to use this code alot in the workbook I'm
working on. Thank you for your time.

"Tom Ogilvy" wrote:

Dim r as Range, r1 as Range
With Worksheets("Data")
set r = .Range(.cells(3,19),.cells(1000,19))
set r1 = .Range(.cells(3,1),.cells(1000,1))

End With

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),r,r1)

--
Regards,
Tom Ogilvy


"Haxer" wrote:

Hi, I don't have any idea of the correct syntax of what I am trying to do but
here it is. On a userform (named search) a person selects a name from a drop
down and hits submit. The idea is to have code in the submit button that
takes that value and does a lookup on it comparing it to data in a sheet. I
don't know how many items will be on the sheet but they could be entered in
column a row 3 to 1000. I do have the code in the macro to sort the items so
a lookup would work. The look uped value should then populate a text box in
another userform. This is what I think i am trying should look like but since
it doesn't work I know that my syntax is probably way off. Can anyone help?

UserFormview.V1.Value =
LOOKUP((UserFormSearch.S1.Value),Sheets("Data").Ce lls(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))