Good morning all:
I am using excel 2003. In attached sample workbook, columns H to K are calculated values. I may have 1000 or more rows of data. All is good but in column J, I want to automate calculations through VBA loop which I am struggling. I know someone of you will be able to solve this puzzle for me. It can be just VBA macro or combination of excel formula. Column J is sample calculations manually done.
Requirements:
1. A constant number: currently: 20 cell N2 but it can be any number from 2 to 250 (means calculations in column J will consider first 20 rows of data from column H & I then put result in M22.).
2. For first calculation for M22, constant value will stay same e.g. 20
3. Need to calculate ABS(I22-H3) for all cells in column I22 till H22.
4. Next, calculated will be place in M23 (move to next cell/row), and M23 will be constant for rest of ABS(I23-H4). This time, I23 & H4 increamented by one.
5. M column is the result of all calculations what I am expecting (same as in column J)
6. If I do manually then for few rows is easy but for thousands of line need some VBA loop.
Attached is my VBA macro attempt and sample worksheet.
Any working solution will be appreciated. Thanks for all fellows in advance.
I am using excel 2003. In attached sample workbook, columns H to K are calculated values. I may have 1000 or more rows of data. All is good but in column J, I want to automate calculations through VBA loop which I am struggling. I know someone of you will be able to solve this puzzle for me. It can be just VBA macro or combination of excel formula. Column J is sample calculations manually done.
Requirements:
1. A constant number: currently: 20 cell N2 but it can be any number from 2 to 250 (means calculations in column J will consider first 20 rows of data from column H & I then put result in M22.).
2. For first calculation for M22, constant value will stay same e.g. 20
3. Need to calculate ABS(I22-H3) for all cells in column I22 till H22.
4. Next, calculated will be place in M23 (move to next cell/row), and M23 will be constant for rest of ABS(I23-H4). This time, I23 & H4 increamented by one.
5. M column is the result of all calculations what I am expecting (same as in column J)
6. If I do manually then for few rows is easy but for thousands of line need some VBA loop.
Attached is my VBA macro attempt and sample worksheet.
Any working solution will be appreciated. Thanks for all fellows in advance.
Code:
Sub MyCalculations()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LR As Integer, CCIVal As Integer, I As Integer, J As Integer, Calc As Double, JLoop As Integer, k As Integer
LR = Range("G" & Rows.Count).End(xlUp).Row
CCIVal = ActiveSheet.Range("N2").Value 'constant value
Range("M3:M" & LR).ClearContents
JLoop = CCIVal + 2 'constant value plus 2, put first calculated value in this cell of column M
'--------------
For I = JLoop To LR 'put first result in M cell then keep going till last row
For J = 3 To JLoop 'keep constant value same but change cell reference of cells H
For k = 0 To CCIVal 'calculate ABS of all rows and sum all results
Calc = Abs(ActiveSheet.Range("I" & JLoop + k).Value - ActiveSheet.Range("H" & J).Value)
Calc = Calc + Calc
Next k
Next J
ActiveSheet.Range("M" & JLoop).Value = Calc / CCIVal
Next I
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub