Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Editing macro

  Asked By: Ella    Date: Oct 05    Category: MS Office    Views: 781
  

I am trying to add 3 more columns to the macro like the following:

ActiveCell.FormulaR1C1 = "FLS"
Range("J1").Select
ActiveCell.FormulaR1C1 = "FLB"
Range("K1").Select
ActiveCell.FormulaR1C1 = "PFB"
Range("L1").Select
ActiveCell.FormulaR1C1 = "LLS"

The headers appear but I can't seem to get the data from these
columns to appear. I had to increase the following :
Range(Cells(lastrow + 1, 4), Cells(lastrow + 1, 12)).Select

I had to increase the Cells(lastrow+1, 9) to the value of 12.
Any ideas on this?

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Fedde Bakker     Answered On: Oct 05

To add  three more columns  (ie only delete the last single column from
the raw data  (odd, because it seems to have more interesting data in
than the extra three you want to add which are just columns of 170s))
do the following:

change
Columns("I:L").Select
to
Columns("L").Select

change
Range(Cells(lastrow + 1, 4), Cells(lastrow + 1, 9)).Select
to
Range(Cells(lastrow + 1, 4), Cells(lastrow + 1, 12)).Select

change
Range(Cells(lastrow + 2, 4), Cells(lastrow + 2, 9)).Select
to
Range(Cells(lastrow + 2, 4), Cells(lastrow + 2, 12)).Select

You have already correctly added 3 more headings. The code for these
12 headings is a bit cumbersome and can be replaced by:

Range("A1") = "Item #"
Range("B1") = "ID SER#"
Range("C1") = "FLA"
Range("D1") = "FLC"
Range("E1") = "PFA"
Range("F1") = "PFC"
Range("G1") = "LLA"
Range("H1") = "LLC"
Range("I1") = "FLS"
Range("J1") = "FLB"
Range("K1") = "PFB"
Range("L1") = "LLS"

In addition there are a lot of lines just re-applying the defaults,
such as:
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False

The .HorizontalAlignment = xlCenter one is the only one doing
anything so this only needs one line like:
Range("A:A").HorizontalAlignment = xlCenter
or
Selection.HorizontalAlignment = xlCenter
etc.

 
Answer #2    Answered By: Taylor White     Answered On: Oct 05

You were correct that I needed one more column to catch all the data
which I added and now have it working. Ther is one more thing that I
wonder if it is possible to add. When the macro  is run could all the
columns with the 170"s be deleted along with the header?

 
Answer #3    Answered By: Cay Nguyen     Answered On: Oct 05

Are the columns  with 170s always going to be the same columns?
or can they be different columns whereupon you would want the macro
to seek out those columns which have nothing but 170s in them and
then delete only those columns? The latter is a bit harder to write.

 
Answer #4    Answered By: Corbin Jones     Answered On: Oct 05

The columns  with the 170's are determined by the type of tests that
are run. The majority of these *.stf files will have data  only in
col. 7 and col 13. It is all determined by the type of meter that is
tested ie. some meters require only the two tests and others may
require up to 9 tests.

 
Answer #5    Answered By: Taylor Evans     Answered On: Oct 05

Replied by sending a file off list, assumes columns  contain 170s are
to be deleted, no other columns are deleted, headings are kept.

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




Tagged: