Compare range values in VBA

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,724
How do I compare range values with VBA?

I have a customer price table in C3:AQ60 and I'm trying to write a macro to compare if the Cell values in Columns are the same on each Column.

There can be up to 20 different values in each column (=up to 20 different ranges within a single column that needs to be compared to the next column and so on till the values change). The columns can be unique but it's most likely they will stay the same for the next couple of columns.

Example (sorry for this but I can't install any new software to my computer):

Column A B C D E F G
row
2 50 100 150 200 250
3 0 10 10 10 10 10 10
4 11 20 10 10 20 30 30
5 21 30 10 10 20 40 40
6 31 40 20 20 20 40 40
7 41 50 20 20 30 40 40
8 51 75 20 20 30 40 40
9 76 100 20 20 40 40 40
10 101 150 50 50 50 40 40
11 151 200 50 50 50 50 50


The columns C and D are identical: The first sub range is C3:D5, next C6:D9 and the third C10:D11. The macro returns the values "50" & "100" from the row 2 and places "50-100" into A3 in a new workbook. Then it returns "0" and "30" from columns A & B and places "0-30" into B3 in the new workbook and the range value "10" to cell C3 in the new workbook. Then it macro takes "31" and "100" from columns A & B and places "31-100" to cell B4 and the matching value "20" to C4 and so on and leaves an empty row when the colum range changes.

The new workbook should look something like this:

Column A B C
row
3 50-100 0-30 10 ("50-100" should be in cell A3, "0-30" should be in cell B3 and "10" in C3)
4 31-100 20 ("31-100" should be in B4 and "20" in C4)
5 101-200 50 ("101-200" should be in B5 and "50" in C5)
6
7 150 0-10 10 (starting from column A)
8 11-40 20 (starting from column B)
9 41-75 30 (starting from column B)
10 76-100 40 (starting from column B)
11 101-200 50 (starting from column B)
12
13 200-250 0-10 10 (starting from column A)
14 11-20 30 (starting from column B)
15 21-150 40 (starting from column B)
16 151-200 50 (starting from column B)


I believe I know how to write the code for getting the values to their places in the new workbook but how do I get the macro to find the actual sub ranges & the matching values within the orginal range?
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,254
Messages
5,769,055
Members
425,513
Latest member
9375021758

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top