Compare columns and insert values in correct place

**HugoRibeiro** · 07-18-2013, 07:53 AM

Hi! So, I have 2 very big columns with numbers that i want to compare. If a number in column C of the worksheet CADIM is also in column B of worksheet sheet3 it's ok, so I go to the next number. When a number is not in sheet3, I want to insert it in the correct position. Both columns are in decreasing order (although they have different sizes).
Example:
columnC(CADIM):
10
9
6
2

columnB(sheet3):
11
10
6
2
1

In this case the only number of CADIM that is not in sheet3 is the 9, so I need to insert a row between row2 and row3 and write 9 in it.
This is what I got so far, I'm only starting to learn VBA and I can't make this work... The current error is the first line in yellow, which I don't know what it means, and when I click the button with the macro nothing happens...

Any help would be appreciated!!

Please Login or Register  to view this content.

**zbor** · 07-18-2013, 07:54 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**nilem** · 07-18-2013, 08:15 AM

Hi Hugo,
try so

Please Login or Register  to view this content.

**flindy87** · 07-18-2013, 08:22 AM

Make sure you put that code into a module and not directly into a sheet

**HugoRibeiro** · 07-18-2013, 09:17 AM

Hey nilem, thanks for such a quick answer!

I opened Sheet3, clicked on view code, put there that code and saved, but do I need to do anything else for it to work? Because when I click on the macro I have nothing happens...
Btw, relating to the code you wrote, do both sheets CADIM and Sheet3 need to be on the same workbook?

**nilem** · 07-18-2013, 10:26 AM

Paste this code into a standard module (Module1). Sheets CADIM and Sheet3 must be in the same book (the active workbook)

**HugoRibeiro** · 07-19-2013, 04:49 AM

It works perfectly!! Thank you so much!! :D

**HugoRibeiro** · 07-19-2013, 05:25 AM

Can you make a small modification? When copying the new values, I would like to copy the cell in column B (which is a small "index" that can be 1A,2A,3A or 4A) together with the one in cell C, so that on Sheet3 I have on column A that index together with the new number on column B. I don't need to compare those "indexes" (because they don't vary with the same number), I just need to compare the number and then insert the new number together with it's index on Sheet3.
Example:
columns B and C (CADIM):
4A 10
3A 9
2A 6
1A 2

columns A and B (sheet3):
4A 11
4A 10
2A 6
1A 2
1A 1

Thanks again!

**nilem** · 07-19-2013, 05:49 AM

try

Please Login or Register  to view this content.

**HugoRibeiro** · 07-19-2013, 06:11 AM

Great!! Thanks a lot!

**HugoRibeiro** · 07-19-2013, 10:53 AM

I'm afraid there's a little problem, sorry to bother again

I was trying the code in a test file, with only 2 columns on each sheet, and it all worked well. However, I tried to implement it on the file I really need, which has a lot of columns on Sheet3, and the code only moved 2 columns down. So I did some alterations to the code (the columns have changed) and this is what I have now:

Please Login or Register  to view this content.

Sheet3 was already done by someone and sheet CADIM was made by me, so now I'm using ONLY columns A and B. The ideal way to do what I need would be to insert an entire row on Sheet3 and then write in columns B and C the corresponding values of columns A and B from CADIM (all the other columns of that row would be left blank).
37em.jpg
What is happening now is it does insert the new values on columns B and C as I want, but it also inserts random (?) values on the rest of the columns. It appears that it copies those values from some other row, but I can't tell why because they are too many.

**nilem** · 07-19-2013, 11:56 AM

Hugo, in this case you need to attach a sample of your file (if your file contains confidential data, replace them with bananas or oranges)

**HugoRibeiro** · 07-19-2013, 12:27 PM

Do you need me to attach one? I attached a printscreen, the rest of the file is just more numbers...

**nilem** · 07-19-2013, 01:20 PM

need a file .xls, not a picture

**HugoRibeiro** · 07-19-2013, 06:39 PM

Sorry, I didn't read well the first time what you said. Here goes the file, maybe you'll find it a bit more complicated than at first.

The sheet I want to write in is "Base de Dados" (the new lines will have the new Numbers and respective Type and the rest of the columns will be left blank, to later be filled manualy one by one), and I want to insert the numbers from sheet CADIM and also from sheet AUTOCAD.
Module3 is the one i'm writing in, you can delete what is there.

Have a nice weekend!

**nilem** · 07-20-2013, 02:12 AM

I made changes in Module1 and Module3 and deleted all the unnecessary from a file.
see attachment

**HugoRibeiro** · 07-20-2013, 07:41 AM

Amazing! Thank you so much, I hope God pays you! :P

Compare columns and insert values in correct place

LinkBack

Thread Tools

Rate This Thread

Display

Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Re: Compare columns and insert values in correct place

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compare 4 column values then insert a 5th columns value to a blank column/cell value

Comparison of a set range of columns on the same sheet to insert correct image on userform

Get distinct values from multiple non contiguous columns and place them in columns

Compare columns in worksheets and move to correct sheet.

How do I create a macro that will compare columns and place data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions