Macro to work on selected columns, not set columns

[nikki.tay81]

I have a macro I created to take info and give me average, standard deviation, the sum of both of those. Then if and statement to give me particular wording with conditional formating. Right now I only have it set to work for columns B-K with all info in set cells. What I work on is in sets of columns by 10s so I want this macro to work all the way across without my having to copr and paste the macro for each set of 10. Here is what I have below:

Selection.End(xlDown).Select

Selection.End(xlDown).Select

Range("C1048574").Select

Selection.End(xlUp).Select

Selection.End(xlDown).Select

Range("C1048572").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"

Selection.Copy

Range("D1048572:I1048572").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("J1048572").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"

Selection.End(xlUp).Select

Selection.End(xlDown).Select

Range("J1048572").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"

Range("J1048572").Select

Selection.NumberFormat = "0"

Range("C1048573").Select

ActiveWindow.SmallScroll Down:=4

ActiveCell.FormulaR1C1 = "=STDEV(R[-1048571]C:R[-2]C)"

Selection.Copy

Range("D1048573:I1048573").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("C1048573").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Style = "Percent"

Range("C1048574").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C[6])"

Range("D1048574").Select

ActiveCell.FormulaR1C1 = "=R[-2]C[6]"

Range("E1048574").Select

ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>5%,RC[-1]),""exclude"")"

Range("E1048574").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="=""misalignment"""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("B1048574").Select

Selection.End(xlUp).Select

Selection.End(xlUp).Select

Range("B1").Select

Selection.Copy

Selection.End(xlDown).Select

Selection.End(xlDown).Select

Range("B1048574").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.TextToColumns Destination:=Range("B1048574"), DataType:= _

xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True _

, OtherChar:="

 

[Hui]

Nikki

I can't look at this in detail now but will later


In the mean time have a look at

http://chandoo.org/wp/2011/06/20/analyse-data-like-a-super-hero/

I think you may be able to use the techniques described there to assist you


In the mean time can you post a small sample of data say only 10 or 20 rows worth

 

[Girish7919]

Hi Nikki,


Can you please attach the worksheet along with this code. This will helps us much better.