VBA FOR loop to increment columns

Question

'Calculating Sum of Max. values of Eth column from pivot table values
    Dim lastRowE As Long
    lastRowE = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    'Excluding grandtotal
    lastRowE = lastRowE - 1
    Worksheets("pmrrcconnmax").Activate
    Cells(1, 5).Value = WorksheetFunction.SumIfs(Range("E7:E" & lastRowE), _
                                        Range("E7:E" & lastRowE), ">0")

 'Calculating Sum of Max. values of Fth column from pivot table values
    'Dim lastRowF As Long
    lastRowF = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    'Excluding grandtotal
    lastRowF = lastRowF - 1
    'Worksheets("pmrrcconnmax").Activate
    Cells(1, 6).Value = WorksheetFunction.SumIfs(Range("F7:F" & lastRowF), _
                                            Range("F7:F" & lastRowF), ">0")

'Calculating Sum of Max. values of Gth column from pivot table values
    'Dim lastRowG As Long
    lastRowG = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    'Excluding grandtotal
    lastRowG = lastRowG - 1
    'Worksheets("pmrrcconnmax").Activate
    Cells(1, 7).Value = WorksheetFunction.SumIfs(Range("G7:G" & lastRowG), _
                                         Range("G7:G" & lastRowG), ">0")

and so on.... upto Kth column (since I am new to VBA). Can someone help me to find the way to put them in for loop or any other suitable loop?

Thank you in advance

yd1 · Accepted Answer · 2016-10-29 20:13:35Z

2

if i understood correctly, and you want to use for in VBA and refer to a different cell each time, try this example:

Dim row As Integer
Dim col As Integer

For row = 1 To 10
    For col = 1 To 10
        Cells(row, col).Value = 1
    Next
Next

it fills the cells in 10x10 with value of 1.

with Cells you can easily insert integer variables to select a cell.

you can also combine it with Range like this example:

Range(Cells(1, 1), Cells(5, 5)).Select

you cal also select a whole column like this:

Columns(5).Select

edited Oct 29, 2016 at 20:13

answered Oct 29, 2016 at 20:05

yd1

2874 silver badges13 bronze badges

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

Comments

Niclas · Accepted Answer · 2016-10-29 22:11:10Z

0

Try this. It will only loop through from E to K.

Option Explicit

Sub Add_Formulas()

'Declaring the variable lColumn as long to store the last Column number
Dim lColumn As Long
'Declaring the variable iCntr as long to use in the For loop
Dim iCntr As Long, iCell As Long
Dim ColLetter As String
Dim lastCol As Long, lastRow As Long
Dim wks As Worksheet

' Set wks so it is the activesheet
Set wks = ThisWorkbook.ActiveSheet

'lastCol = wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column
' Using column D as you are using column no. 4 in your code
lastRow = wks.Range("D" & wks.Rows.Count).End(xlUp).Row

'Excluding grandtotal
lastRow = lastRow - 1

'Assigning the last Column value to the variable lColumn
'lColumn = lastCol
lColumn = 11

'Using for loop
' 5-11 = E-K
For iCntr = lColumn To 5 Step -1

    ColLetter = GetColumnLetter(iCntr)
    Cells(1, iCntr).Value = WorksheetFunction.SumIfs(Range(ColLetter & "7:" & ColLetter & lastRow), _
                                         Range(ColLetter & "7:" & ColLetter & lastRow), ">0")
Next

End Sub

Function GetColumnLetter(colNum As Long) As String
Dim vArr
vArr = Split(Cells(1, colNum).Address(True, False), "$")
GetColumnLetter = vArr(0)
End Function

edited Oct 29, 2016 at 22:11

answered Oct 29, 2016 at 20:14

Niclas

1,3024 gold badges19 silver badges35 bronze badges

3 Comments

Ganesh Prasad B K Over a year ago

thank you so much for your support..........i myself finally came up with a code below (after a lot of trial & error method) which is somewhat 'optimized' i think.......

Ganesh Prasad B K Over a year ago

'Calculating Avg. of Max. pivot table values Dim i As Integer Worksheets("pmpdcp").Activate fRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row 'Excluding grandtotal fRow = fRow - 1 For i = 5 To 12 Dim rng As Range With Worksheets("pmpdcp") Set rng = .Range(.Cells(7, i), .Cells(fRow, i)) Cells(1, i).Value = WorksheetFunction.AverageIfs(rng, rng, ">0") End With Next i

Niclas Over a year ago

You should remember to declare variable names and their type (Dim). Also, this will fail if you do know have data in rng. To do some error handling, you can use If Application.WorksheetFunction.Count(rng) > 0 Then` <-- new line, your work sheet function here --> End If

Collectives™ on Stack Overflow

VBA FOR loop to increment columns

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related