Peskarik
08-14-2008, 12:28 AM
Hello,
I usually never use VBA or Excel, doing everything in R, as
I am econometrician. :whistle:
But this time I have to automate another person's Excel file that contains lots
of info, formulas, and plots.
The problem I have is following:
I have a sheet with an array of say (1000x20), the 20 columns
are named. Some of these columns contain data that never changes,
some contain data that must be filled in by hand, some contain
formulas that take data from other columns in the array.
Array is organized as follows:
one can see it as a collection of smaller arrays of (36x20). Sometimes
it can be (34x20) or (35x20), that is, number of rows vary.
Column NAME contains names of the companies, column DATE contains
date formatted like 01.01.1998. There is column ID, which contains
numeric ID for each company. Then there are columns that contain
formulas etc.
The smaller arrays are stacked, which means each array is for one
particular company, so in column NAME there are 36 occurencies
of one particular name, then 36 occurencies of another and co on.
Same goes for column ID. In column DATE there are 36 past months for each company.
The formula columns take data from other columns for each particular
firm.
WHAT I NEED TO DO EFFICIENTLY:
I need to write a piece of VBA code that inserts a new row
at the bottom of each smaller array (that is the row for
the new month), then fill in the cells in the row accordingly
That is copy in the name and the ID of the company
in columns NAME and ID, the new date (say 01.09.2008) in the DATE
column, and copy formulas from the row above.
I have done this with Do Until Loop, If statement, and using Offset
function a lot. My code finds column NAME and then it compares
each cell value in that column going down with the previous cell value,
and when they differ I know the array for the next company starts
and I insert new row, then Autofill date (using offset), copy name
and ID from cells above, copy formulas.
This is very slow!!! :banghead: :dunno
Anybody can suggest another, faster way to do these operations,
maybe counting similar IDs, and jumping to the last row containing
each particular ID, inserting rows, copying formulas "in bulk", etc
Really need some help, people.:help
Thank you in advance!
Regards,
Sergey
I usually never use VBA or Excel, doing everything in R, as
I am econometrician. :whistle:
But this time I have to automate another person's Excel file that contains lots
of info, formulas, and plots.
The problem I have is following:
I have a sheet with an array of say (1000x20), the 20 columns
are named. Some of these columns contain data that never changes,
some contain data that must be filled in by hand, some contain
formulas that take data from other columns in the array.
Array is organized as follows:
one can see it as a collection of smaller arrays of (36x20). Sometimes
it can be (34x20) or (35x20), that is, number of rows vary.
Column NAME contains names of the companies, column DATE contains
date formatted like 01.01.1998. There is column ID, which contains
numeric ID for each company. Then there are columns that contain
formulas etc.
The smaller arrays are stacked, which means each array is for one
particular company, so in column NAME there are 36 occurencies
of one particular name, then 36 occurencies of another and co on.
Same goes for column ID. In column DATE there are 36 past months for each company.
The formula columns take data from other columns for each particular
firm.
WHAT I NEED TO DO EFFICIENTLY:
I need to write a piece of VBA code that inserts a new row
at the bottom of each smaller array (that is the row for
the new month), then fill in the cells in the row accordingly
That is copy in the name and the ID of the company
in columns NAME and ID, the new date (say 01.09.2008) in the DATE
column, and copy formulas from the row above.
I have done this with Do Until Loop, If statement, and using Offset
function a lot. My code finds column NAME and then it compares
each cell value in that column going down with the previous cell value,
and when they differ I know the array for the next company starts
and I insert new row, then Autofill date (using offset), copy name
and ID from cells above, copy formulas.
This is very slow!!! :banghead: :dunno
Anybody can suggest another, faster way to do these operations,
maybe counting similar IDs, and jumping to the last row containing
each particular ID, inserting rows, copying formulas "in bulk", etc
Really need some help, people.:help
Thank you in advance!
Regards,
Sergey