Asked by:
Iterate through columns
Question

Hi all,
I have a spreadsheet of data. For each column I need to do different statistical analysis for. Now I know you can iterate through the different rows using a code like this:
rowmax = Worksheets("Serial Numbers").Range("B65536").End(xlUp).row
For rowindex = 3 To rowmax
Sheets("Stats").Select
Worksheets("Serial Numbers").Range("B" & rowindex).Copy
Sheets("Stats").Range("C3").PasteSpecial xlPasteValues
NextAnd it will go down and look at each row one by one. But now instead of doing this for rows, I want to go column by column. For the code to look at Column A and do some equations and what not and when done with column A will then move onto column B and so on. Does anyone have any way that I can do this?
All replies

You could use code like this:
Dim ColIndex As Long
Dim ColMax As Long
Dim ws As Worksheet
Set ws = Worksheets("Serial Numbers")
' Last used column in row 3
ColMax = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
' Loop from column 2 (B) to the last used one
For ColIndex = 2 To ColMax
ws.Cells(3, ColIndex).Copy
Worksheets("Stats").Range("C3").PasteSpecial Paste:=xlPasteValues
...
Next ColIndexRegards, Hans Vogelaar (http://www.eileenslounge.com)

How does this look?
Dim r As Integer
Dim ColIndex As Long, ColMax As Long
Dim sum As Double, mean As Double
Dim ws As Worksheet
Set ws = Worksheets("Stats")
ColMax = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column
rowmax = ws.Range("C65536").End(xlUp).rowFor ColIndex = 9 To ColMax
sum = 0
For r = 8 To rowmax
sum = ws.Cells(r, ColIndex).Value + sum
Next r
mean = sum / (rowmax  7)
ws.Cells(ColIndex, rowmax + 1).Value = mean
Next ColIndex
End Sub 
You could use the builtin worksheet function Average:
Dim RowMax As Long Dim ColIndex As Long, ColMax As Long Dim ws As Worksheet Set ws = Worksheets("Stats") ColMax = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column RowMax = ws.Range("C" & ws.Rows.Count).End(xlUp).Row For ColIndex = 9 To ColMax ws.Cells(ColIndex, RowMax + 1).Value = _ Application.Average(ws.Range(ws.Cells(8, ColIndex), _ ws.Cells(RowMax, ColIndex))) Next ColIndex
Regards, Hans Vogelaar (http://www.eileenslounge.com)


Yes: Application.StDev_P calculates the population standard deviation of a range, and Application.StDev_S the sample standard deviation.
See https://msdn.microsoft.com/enus/library/office/ff822194.aspx for a list of worksheet functions available in VBA.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Edited by Hans Vogelaar MVPMVP Friday, February 6, 2015 11:38 PM