(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!
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?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That would be suicide as I am going to have 1100 excel files over next one year period.Originally Posted by JBeaucaire
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!
Last edited by pban92; 02-26-2010 at 06:57 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks