Adding the same action to every second line

[Solved/Closed]
Report
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
 Dg83 -
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can use formula too
Which please?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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!
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Sorry I missed the "5" part

You can do this

=A1 + (5* (MOD(ROW(),2)))
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

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!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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