CODEWITHSUNDEEP
vbaexcel
Bobby Jalloh
Bobby Jalloh

Reputation: 29

Loop through a column

I am trying to loop through a column in excel using VBA, am having a problem because has "W2" hard coded in the function so as I iterate through the spreadsheet, the W2 needs to change to W3, W4 W5 etc.

below is my code.

Function GetTopDrTS(L_NumCellsFromEdge As Integer, L_NumOfCells As Integer) As Double
  'Get Top Drive Side Triple Spot calculation

  Dim val As Double
  val = 0

  'Select Cell W2 to set starting position
  Range("W2").Select

  'Read in the cells we need to average
  Dim i As Integer
  For i = 0 To L_NumOfCells - 1
    val = val + Selection.Worksheet.Cells(Selection.Row, _
                Selection.Column + EdgePos + L_NumCellsFromEdge + i).Value
  Next i

  GetTopDrTS = val / L_NumOfCells

end function

Upvotes: 2

Views: 124

Answers (2)

Comintern
Comintern

Reputation: 22185

I'm not sure why you need to loop at all. Your function is just calculating an average, so use the Average function instead. That reduces your function to one line:

Function GetTopDrTS(L_NumCellsFromEdge As Integer, L_NumOfCells As Integer) As Double

    GetTopDrTS = WorksheetFunction.Average(Range("W2:W" & L_NumOfCells + 1))

End Function

Now that it's exactly one line, I'd get rid of the function entirely and just use the WorksheetFunction anywhere you'd normally call GetTopDrTS.

Note that it isn't clear from the question or the code what EdgePos and L_NumCellsFromEdge are supposed to be - if they are intended to replace the hard-coded column "W", it would be fairly easy to replace it with an offset.

Upvotes: 1

FreeMan
FreeMan

Reputation: 5687

Try this:

  'Read in the cells we need to average
  Dim i As Integer
  For i = 0 To L_NumOfCells - 1
    range("W" & i).select
    val = val + Selection.Worksheet.Cells(Selection.Row, _
                Selection.Column + EdgePos + L_NumCellsFromEdge + i).Value
  Next i

Upvotes: 0

Related Questions