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?
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:
