Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Recorded macro editing

  Asked By: Willie    Date: Oct 07    Category: MS Office    Views: 760
  

I recorded this macro. The purpose is to open a file then reallign
the data into columns and then compute the average and standard
deviation on each col. It is recorded for 28 entries. How does one
set this up to accept ie 55 or 83 or 110 entries with out cutting and
pasting. i would appreciate any help.

Keyboard Shortcut: Ctrl+y
Rows("1:17").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(25, 1), Array(33, 1),
Array(41, 1), Array(49, 1), _
Array(57, 1), Array(65, 1), Array(73, 1), Array(81, 1), Array
(89, 1), Array(97, 1), Array( _
105, 1))

Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Item #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "ID SER#"
Range("C1").Select
ActiveCell.FormulaR1C1 = "FLA"
Range("D1").Select
ActiveCell.FormulaR1C1 = "FLC"
Range("E1").Select
ActiveCell.FormulaR1C1 = "PFA"
Range("F1").Select
ActiveCell.FormulaR1C1 = "PFC"
Range("G1").Select
ActiveCell.FormulaR1C1 = "LLA"
Range("H1").Select
ActiveCell.FormulaR1C1 = "LLC"
Range("I1").Select
ActiveCell.FormulaR1C1 = "FLS"
Range("C1:I29").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("30:31").Select
Selection.Delete Shift:=xlUp
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A29"),
Type:=xlFillDefault
Range("A3:A29").Select
Range("A2:A29").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("C30").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-28]C:R[-1]C)"
Selection.Copy
Range("D30:I30").Select
ActiveSheet.Paste
Range("B30").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "X"
Range("B30").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("B31").Select
ActiveCell.FormulaR1C1 = "STDEV"
Range("B31").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("C31").Select
ActiveCell.FormulaR1C1 = "=STDEV(R[-29]C:R[-2]C)"
Selection.Copy
Range("D31:I31").Select
ActiveSheet.Paste
Range("C31").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D33").Select
End Sub

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Frank Butler     Answered On: Oct 07

It all seems to hinge on this line:
Range("C1:I29").Select
How is this range determined?
Is it all the rows that have been imported (after deleting the first
17 rows) or is it a number of rows selected by you manually? I don't
want to waste my time or yours altering your code inappropriately.
If it's the former I'll be using the vba equivalent of pressing the
end key followed by one of the arrow keys, if it's the latter I may
arrange for you to select which rows are to be processed before the
macro is run.

 
Answer #2    Answered By: Francis Riley     Answered On: Oct 07

What kind of file  are you opening? And could you explain the process
a little more? I see all kind of deletes, defining of headings (each
time the VBA-sub runs?), formatting etc.

 
Answer #3    Answered By: Alan Palmer     Answered On: Oct 07

That's easy:
Delete rows 1 to 17
Insert a new row at the top
Do a Text to columns  procedure on column A
Delete columns I to L
Insert a new column A
Add column headings in cells A1 to I1
Select the block of data  in the middle and centre the text/value
Delete the bottom 2 rows (not sure what's in there)
Number the data rows in column A 1 to x
Put an Average formula at the bottom of column B
Copy it across the bottom of the other columns
Put an X in column A on that row
Add a STDEV formula to the bottom of Column B
Copy it across the bottom of the other columns
Add 'STDEV' as text to the leftmost cell on this row.

 
Answer #4    Answered By: Guadalupe Rogers     Answered On: Oct 07

Sorry for not giving enough info. The file  used is a .stf file.
This file is created from an old dos based program (written in pascal-
I believe). The program is used for testing electricity meters. The
data is then required to do statistical analysis. Basically you are
describing the method I currently use. However I am trying to
automate the system as much as possible as we do hundreds of these
groups. I have not had time to try some of the suggestions previously
posted but when I do i will get back to you.

 
Answer #5    Answered By: Gustavo Taylor     Answered On: Oct 07

OK, could post the file  via sending it to one of the moderators?

 
Didn't find what you were looking for? Find more on Recorded macro editing Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post