No VBA Experience, Looking to do a VLookup for what's in a cell and to copy the row?

[richlilrich]

Hello,
I Appologies first of comming onto the community with virtually no VBA experience or understanding.

I am planning to get a Button to run Looking at cell "T4" and whatever value is in that sell say "699" or "123" It searches from g20-g1000 and everything with that number it copies the row to place in another table on the same page. say "X4"

There is more to it but i think i can take it from there with some google and basic formula knowledge.
I found a site that i was reading into but instead of a "keyword" i wanted to use the value what was in a cell.

http://www.techonthenet.com/excel/ma...for_string.php


I understand if you need more information and i may not be very good at explaining it but any sites that help out or a code i can quickly edit would be helpful.

Seeing as i'm starting to use excel more can someone also recommend me somthing where i can learn VBA without boring the hell out of me?
How did you guys learn it?

Many Thanks,
Rich

[FDibbins]

Hi, welcome to the forum

1st of all, let me say that we have no problem with anyone have minimal knowledge/experience in any of excels aspects, so no apologies needed

OK...
not sure you really need VBA for this, you can probably use some regular formulas for what you want.
Will there be more than 1 instance of your search criteria?

	A	B	C
1	acc no	Inv no
2	200687	1	a
3	200579	2	b
4	200506	3	c
5	200461	4	d
6	200461	5	e
7	200459	6	f
8	200459	7	g
9	200613	8	h
10	200462	9	i
11
12
13	acc no	Inv no
14	200459	6	f
15		7	g

B14=IFERROR(INDEX(B$2:B$10,SMALL(IF($A$2:$A$10=$A$14,ROW($A$2:$A$10)-1),ROWS(A$2:A2))),"")
ARRAY formula, copied down and across...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Leith Ross]

Hello Rich,

Welcome to the Forum!

This macro will copy the contents of the cells "G20:G100" of the ActiveSheet that match the contents of cell "T4" of the ActiveSheet. The cells in "G20:G1000" that match the value of cell "T4" are copied to column "Z" starting in row 2.

The code should be easy to understand. If you have any questions, ask me for help.

Please Login or Register  to view this content.

[richlilrich]

Hey guys, I wasn't expecting such a quick responce.
Many thanks.

i Will be using it more than once instance.
for example
Capture.PNG

it will sort all the names out into different tables located at different parts or the spreadsheet but on a lot bigger scale.

Ross i will give this ago in the morning tomorrow your code can i just copy and past and change the T4 value to T5, T6 and so on so it dose it for more than 1 search? just take off the loop i'm guessing. within the button function.

i Also like the message box for no results found but may not be needed in this case maybe a more finish message box to stay professional.

looks like this one is almost solved Many thanks guys

can anyone recommend me somthing that i can use to start learning VBA?

[Leith Ross]

Hello Rich,

I would be easier for me to answer your questions looking at the workbook.

Please change or remove any sensitive information from the workbook before posting it.
How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[richlilrich]

Working Progress.xls

So i need it to run and paste along z2 then for the next one t5 to paste on z 22 and so on and so fourth.

From there i can do some basic excel work to finish it off once the information has been placed in the Z.

Many thanks for your help again.

[richlilrich]

I've almost got it working
Thanks again.
Just need to get it to copy the row.

[richlilrich]

I'm still unable to find a code to enter so it will copy the the row then paste it in and not just that section.

[Leith Ross]

Hello Rich,

Which worksheet are you referring to?

[richlilrich]

So on the "DAY" if you include your code into the button it will bring up ID number 1 3 times just need it to also include k20 to r20 information when copied across. so basicaly the whole of row 20 copied and done the same for the next result it finds.
untitled.JPG

[Leith Ross]

Hello Rich,

Now I am really confused. First, you have written your own macro. Mine is no longer in the workbook. Second, I have no idea where this data is coming from for columns "K:R" as these columns are blank on the "DAY" worksheet.

Let me know when you are finished tweaking the worksheets and are ready to create a macro.

[Leith Ross]

Hello Rich,

Now I am really confused. First, you have written your own macro. Mine is no longer in the workbook. Second, I have no idea where this data is coming from for columns "K:R" as these columns are blank on the "DAY" worksheet.

Let me know when you are finished tweaking the worksheets and are ready to create a macro.

[richlilrich]

Don't worry if the information is blank there should be somthing filled in on row 20. i was just using it as an example.
I'm just using it as an example
the only extra thing i need your macro to do is instead of copying what it finds in the "g" is to also copy the row that it's on. if that is possible. so whatever information is enterd it displays across "z2" across to the right wher ether eis space.

[richlilrich]

Sorry I'm realy back at explaing this stuff. and all the confusion i have caused.

Once i got the code working i can tidy it up and place it where suited just changing the cell locations