Help with Vlookup VB code

[duds]

Hi,

Ive searched and searched for the answer to this query but I dont seem to get any where so I have resorted to posting it - sorry if this has been covered else where.

What I am looking for is VB code to firstly have a message box with an input box on (that bit i can do). The text and number put into the message box is then used to search the table for the information that relates to it. The problem I am having is getting all the information as each one varies.

The ideal would be for the code to find the data and then copy it either to the clipboard or to a section of the workbook.

The tables I am searching can be put into which ever format is easiest. Below is an example table;

A B C D E
1 A.1 Side Road Correctly Left

2 A.2 Hit in Rear Left

3 A.3 No known Police ref

4 A.4 Vandalism


Any help is greatly appreciated.

Thanks

 

[Mike Blackman]

Hi And welcome to the board,

Maybe something like this;

x = InputBox("Please enter your text here")

MsgBox Application.vlookup(x, Sheets(1).Range("A1:E100"), 5, 0)

 

[duds]

Thanks for the prompt response Mike.

I started using that code however I dont know how to tell it - once inital search found to then carry on looking to the right until it finds a blank cell and then copys all the data to the clip board?

 

[Mike Blackman]

Hi,

Maybe something like this then;

Sub find()

x = InputBox("Please enter your text here")

ActiveSheet.Range(Cells.find(x), Cells.find(x).End(xlToRight)).Copy [B]ActiveSheet.Range("A1")[/B]

End Sub

You'll need to change the Bold part which is the paste destination.

 

[duds]

Does the cells.find replace vlookup?

Sorry for my ignorance! Not come across that before...

Thanks again

 

[Mike Blackman]

Hi,

A VLookup will retrieve the Value of one Cell only, I got the impression you were trying to find all the cells to the right of the cell you have matched with your InputBox? Is this not correct?

 

[duds]

Yes you are correct Mike. Previously I had used multiple V lookups to perform the same thing. All i did was change the location of where I wanted it to look. Much easier your way!

Cheers

 

[duds]

Ok sorry Im sure Im not being helpful here but I cant get that to work.

Other then the pasting part should I have edited any other bits of the code?

When going through the code it doesnt seem to be picking up the search function?

 

[Mike Blackman]

Hi,

What sheet & Range are you looking to find the match and what sheet and range do you wish it to copy to? What did you try (with your alterations)?

 

[duds]

Hi

All I altered was the bold text. I just removed the ActiveSheet.Range("A1") so that it would just remain in the clipboard. (probably the wrong thing to do!)

Sub find()

x = InputBox("Please enter your text here")

ActiveSheet.Range(Cells.find(x), Cells.find(x).End(xlToRight)).Copy ActiveSheet.Range("A1")

End Sub

Range would just be column A to column G. I havent transposed all of the data yet so dont have an exact range to input. Sheets havent been renamed. Sheet1 is where I am putting the table. So the pasted data can just go onto Sheet2 starting A1.

Thanks for the continued support!