Using the contents of a range

[globalzen]

Hi Guys,

As you will see from the following question - I am definitely a new user to vba.

I want to search a table for a string and for the first occurence found I want to be able to use the data in the cell three cells to right. Eg if A1 = "eggplant" and A3 = "cook with salt" I want to be able to search for "eggplant" and return in a msgbox "cook with salt".

Could someone kindly give me a few tips.

Thanks in advance
Gary

[royUK]

Why would you need VBA for this? A simple VLOOKUP formula would work

[globalzen]

Why would you need VBA for this? A simple VLOOKUP formula would work

Because actually, I guess I was trying to simplify what I was trying to do. My goal is actually to produce a form of sorts where you put in a student number then format the number. A macro then searches various sheets for the student number and consolidates formatted search results into either a couple of cells at the top of a table or into a form.

I thought that my first step would be to be able to use find (or application.vlookup) on a string get the resultant range and use the data in the row for each cell in the range.

So if I have a table where;

A1 = "112233" and B1 = "English"

Please Login or Register  to view this content.

output would be a range with the first cell in that range referencing A1, how do I assign to a string variable the value of the column beside A1 (ie B1)?

Thanks