VBA: increment a variable? April 23rd, 2004, 08:29 I would like to loop through a range and then if the value of the cell to the right is ' (nothing) then I want to save that into a variable. VBA code: Auto increment cell value after each printing: Sub IncrementPrint() 'updateby Extendoffice 20160530 Dim xCount As Variant Dim xScreen As Boolean.
'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. It will only loop through from E to K. Option Explicit Sub AddFormulas '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.
I understand about readability but a separate line for each item in quite a large array seems, to me anyway, a bit much. Whenever I use Array with arrays that size I try and make them readable, but I also try to make sure they fit in 1 screen.
You could end up with a column of string values going down the centre with nothing to show what they actually are. I wouldn't recommend using what I suggest for much else. For example if it was a long SQL statement in the code, building it up by line by line using concatentation can be a good idea.
In that case it can actually help debugging the code - it's much easier to work with an SQL statement that's been broken down to it's individual parts/clauses. By the way what are those formulas meant to do?