Loop through a column

Question

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

Comintern · Accepted Answer · 2015-04-30 23:28:26Z

1

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.

answered Apr 30, 2015 at 23:28

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bobby Jalloh Over a year ago

That's only going to give me an average of w2, however i wanna be able to get the average W2, w3,+ .... + w1326. maybe i should post my entire code

Comintern Over a year ago

@BobbyJalloh - No, it will give the average of whatever range it generates based on the input parameter L_NumOfCells. If you L_NumOfCells = 100, it evaluates to WorksheetFunction.Average(Range("W2:W101")).

FreeMan · Accepted Answer · 2015-04-30 17:30:51Z

0

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

answered Apr 30, 2015 at 17:30

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

2 Comments

Bobby Jalloh Over a year ago

did you mean range("W2" & i).select ?

FreeMan Over a year ago

No, I didn't. Range("W2") is a hard coded reference to a single cell, and that's what's causing your code to not loop. Range("W" & i) removes the hard coded row number and replaces it with the variable i which is changed each time through your loop. Range("W2" & i) would also be not hard coded, but would give you rows 21-29, then 210-299, then 2100-2199, etc, as i increments - probably not what you're after. Give it a try, see what happens, then step through the code in the debugger (hit the F8 key) to execute one line at a time & see what cell is being selected.

Collectives™ on Stack Overflow

Loop through a column

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related