(...) So if the user enters the value "23" in the search field. If "23" is found in cell A49 I would like it to return the value in cell C49. What code will help me do this? Thank in advance.
i'll asume your A column has no empty spaces, so i can use the END method:
- dim a
-
dim i as long
-
with worksheets("sheet1")
-
a = range(.cells(1,3), .cells(1,1).end(-4121))
-
end with
-
for i = 1 to ubound(a)
-
if a(i,1) = textbox1.text then exit for
-
next
-
if i > ubound(a) then
-
msgbox("number not found") : exit sub
-
end if
-
msgbox a(i,3)
you can use cells(i,1) in your FOR and return cells(i,3), but its quite faster to import the entire range into a variant and work with it. (in this case 'a' is a variant, since is the default type, but you can also define it: Dim a as variant)
HTH