none
Iterate through columns RRS feed

  • 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

     
     Next

    And 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?

    Friday, February 6, 2015 9:27 PM

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 ColIndex


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 6, 2015 9:44 PM
  • 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).row

    For 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

    Friday, February 6, 2015 10:22 PM
  • You could use the built-in 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)

    Friday, February 6, 2015 10:54 PM
  • Is there a built-in worksheet function that will do the standard deviation for me?
    Friday, February 6, 2015 11:03 PM
  • 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/en-us/library/office/ff822194.aspx for a list of worksheet functions available in VBA.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, February 6, 2015 11:37 PM