Is it possible...lookup macro

[Guest]

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").Cells(3,
19:1000,19),Sheets("Data").Cells(3, 1:1000,1))

 

[Guest]

I have solved situations like this by placing the lookup formula in a cell
that It will never be used say IV1, then referencing that cell value in the
form.

UserFormview.V1.Value =Range("IV1").value

Regards,

Michael Arch

 

[Guest]

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)

 

[Guest]

Thank you for your response. That much I do know and I guess that will work
if I have to do it that way. Basically I am trying to get a better grasp on
macros (and formulas) in Excel and I guess trying to streamline my work a
little more. The problem is that while I am trying to do these things I don't
always know what is possible and what isn't. Thanks again though.

 

[Guest]

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!

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.