New to writing macros

Hello,

I need to write a macro that compares the numerical value of 2 cells and give me a result as described below:

All the numbers would be either whole numbers or whole numbers + 0.5

Example 1:

The two values are both whole numbers then no action is taken.

Cell A = 5
Cell B = 5

or

Cell A = 7
Cell B = 5

Result = No action taken

Example 2

The two values are both equal half numbers then 0.5 is subtracted from one number and 0.5 is added to the other number to make them both whole numbers.

Cell A = 5.5
Cell B = 5.5

Result = 0.5 is subtracted from Cell A to make it 5, 0.5 is added to Cell B to make it 6.

Example 3

The two values are unequal half numbers then 0.5 is subtracted from the number with the lower value and 0.5 is added to the number with the higher value to make them both whole numbers.

Cell A = 5.5
Cell B = 8.5

Result = 0.5 is added to Cell A to make it 6, 0.5 is subtracted from Cell B to make it 8.

I hope this makes sense.As I mentioned, I'm just learning to write macros so any help is GREATLY appreciated.

~Donny

Hi Donny,

I think this code should do what you're asking:

Please Login or Register  to view this content.

Substitute A1 and B1 for your two cells. You can have this code run from a command button or a shortcut key (or by manually running through the Macros screen).

It's probably not working because your first test (value1 and value2) are both whole numbers - which tells the macro to 'Exit Sub', so no more code runs.

Had I known you were going to compare 48 cells in a vertical column I'd have written the code differently, using a loop. I'll see what I can put together and get back to you.

Donny, try this code instead. It uses a loop, and you only need to declare the variables once, not for every pair of cells.

Please Login or Register  to view this content.

You can't change the 60, 61, 106 and 107 to E60, E61, E106 and E107. That's causing the type mismatch -- you're taking numbers and converting them to text, when the variable is expecting a number.

Please be sure that when you request assistance, you are specific. Your first example showed that you wanted to compare cells in Column D, so I coded it that way. To change it to column E, use the following code (notice the red 5's, which represent column E, while the original 4's represented column D):

Please Login or Register  to view this content.

Hi donny,

Are you now trying to compare two adjacent rows in column G and separately compare two adjacent rows in column H? Or are you trying to compare cells in columns G vs. H as well?

For clarity, it may just be easier to keep the two columns separate, like so:

Please Login or Register  to view this content.

I also changed your 'For k=61...' line to 'For k=60 to 106', since I assumed cells i and k started at the same row, while cells j and l started the next row down (j=61, l=61).