By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Excel VBA question (Userform search field)

P: 7
I have a userform that is used as an interface for information stored on excel spreadsheets. The user enters bits of information into fields on the userform which are place on the corresponding worksheet. I would like to have a txtBox that is used as a search field. When the user enters a number in the field it will search through column A of worksheet 1 for the value that was entered into the field. Once it is found I would like it to return the value of the cell that is two rows to the right of the found cell. 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.
Jan 16 '08 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
(...) 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:

Expand|Select|Wrap|Line Numbers
  1. dim a
  2. dim i as long
  3. with worksheets("sheet1")
  4.     a = range(.cells(1,3), .cells(1,1).end(-4121))
  5. end with
  6. for i = 1 to ubound(a)
  7.     if a(i,1) = textbox1.text then exit for
  8. next
  9. if i > ubound(a) then
  10.     msgbox("number not found") : exit sub
  11. end if
  12. 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
Jan 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.