Adding the same action to every second line

Solved/Closed
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018 - Jan 13, 2013 at 04:26 PM
 Dg83 - Jan 15, 2013 at 01:11 AM
hi guys,

i wonder if you could help me with the following:

there is a worksheet where column A is full with numbers. I would like to add to every second row 5. So if there is 10 in cell A1 I would like to see 15 there, if A3 15 the desired result is 20. Is there a way to create a macro that would be able to perform this string of action lets say until cell A50?

thanks a lot!

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 13, 2013 at 04:34 PM
you can use formula too
Which please?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 07:49 AM
if number you want to add is on odd row, then you can use
=A1 + MOD(ROW(),2)

if you want to add for those rows which are on even row then you can use
=A1 + MOD(ROW()+1,2)
Thanks! Its absolutely clear now!
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018
Jan 14, 2013 at 12:52 PM
thanks a lot but it does not seem to return the required result.
Lets take this simple example: A1 is 1, A2 is 2, A3 is 3. I would like to add 5 to the odd rows, so B1 should return 6 and B3 8. Since A2 is an even row it should remain 2.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 01:27 PM
Sorry I missed the "5" part

You can do this

=A1 + (5* (MOD(ROW(),2)))
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018
Jan 14, 2013 at 03:12 PM
thanks, it has worked well! I just wonder why there is a multiple sign after 5 when it adds this amount to the required cells. What is the logic behind it please?
Thank you once again, my issue is resolved!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 03:17 PM
Row() is a function that gives the row number on which the function is called

mod is a function that returns a remainder. It has two parameter. First the number that you want to diviide and 2nd the number by which you want to divide

So mod(4,2) is saying divide 4 by 2 and return me the remainder which is 0

mod (1,2) is saying that divide 1 by 2 and return me the remainder which is 1

so dividing any number by 2 tells me if a row is odd (remainder would be 1) or even (remainder would be 0)

Any thing multiplied by 1 remains same, and any thing mulitiplied by 0 becomes 0

so 5 * 1 would be 5
and 5 * 0 would be 0
that answer i am adding to A1 to get the answer

hope it clarifies for you