Macro scripts to calculate cell value and exporting file format type

[pban92]

(Pls note this post has a single excel attachment!)

I have 10 excel files. Each file has 24 columns. Each two columns form a group (such as A and B, C and D and so on). Finally, each group has uneven number of rows.

I need to add two corresponding cells of each group and put the value to the corresponding cell of a new colum. I know it is fairly easy by dragging the formula cell (you know what I mean). I need to repeat the procedures for all the groups and for all the 12 excel files I have. Obviously creating macro is the best option. BUT,


1. as I have uneven number of rows between any two groups, how can I handle this problem and create a macro script? AND

2. what is the best file extension type to export the data file? (Exporting as formatted dilimited text file makes the data file messy as the column of smaller number of rows takes the values from the column of higher number of rows.)

Any commnet/suggestion/hint/example would be highly appreciated!

Many thanks!

[JBeaucaire]

I would:

1) Cut/Move each pair of rows to a new workbook
2) Add the 3rd column of sums
3) Save the new workbook as a text file, need a naming convention for the new files and folder path
(no longer affected by other longer pairs of columns)
4) Close the new workbook
5) Repeat with each pair until all are gone
6) Repeat with next workbook

Can you do that?

[pban92]

I would:

1) Cut/Move each pair of rows to a new workbook
2) Add the 3rd column of sums
3) Save the new workbook as a text file, need a naming convention for the new files and folder path
(no longer affected by other longer pairs of columns)
4) Close the new workbook
5) Repeat with each pair until all are gone
6) Repeat with next workbook

Can you do that?

That would be suicide as I am going to have 1100 excel files over next one year period.

[pban92]

Ok I boiled down the problem to a simpler one. Say, I have Column A with unknown number of rows. :
A B
1
2
3
4
.
.
.
n

I want
Column B = Column A +1 until it reaches the n-th row (notice the (n+1)-th row is empty).


I have made the following macro script but not working

*********************************************************

Sub Test4()

Range("B2").Select
Do Until IsEmpty(Range("A2"))
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop

End Sub
***************************************************************

But this calculates whole range of Column B taking the empty cell of Column A as 0 value! Any suggestion how to work on it pls?




Many thanks!