Using offset to copy formula down a column

Question

I am trying to apply a formula where I need to refer to a few cells to the left of the formula cell and a few cells to the up and apply the formula down the column for quite a few rows.

I am trying to achieve the result by using offset function, but I am not getting the desired result.

Sub ResultAchievedIsNotAsRequired()

Dim variableA As Long
variableA = Worksheets("DATA").Range("D7").Value

Dim LeftRow As Long
LeftRow = ActiveCell.Offset(0, -1).Value

Dim Upperrow As Long
Upperrow = ActiveCell.Offset(-1, 0).Value

Dim q As Long
Worksheet("MyCalculation").Range("B4").Select

For q = 4 To 50
    Selection.Value = (ActiveCell.Offset(0, -1).Value * (2 / (VariableA + 1)) + ActiveCell.Offset(-1, 0).Value * (1 - (2 / (VariableA + 1))))
    ActiveCell.Offset(1, 0).Activate
Next

End Sub

====
I am really sorry my internet connection for down for a couple of days.
I should have phrased the question a bit more precisely.

Cell B3 should have value equaling =>average of A1 to A3.
Cell B4 should calculate : (value of cell A4*(2/(8+1))+ value of cell B3*(1-2/(8+1)))
Cell B5 should calculate : (value of cell A5*(2/(8+1))+ value of cell B4*(1-2/(8+1)))
Cell B6 should calculate : (value of cell A6*(2/(8+1))+ value of cell B5*(1-2/(8+1)))
and so on….down the column

The screen shot attached was just to give an example of a similar problem where calculations were almost of the same nature though, column referred to in formula were different. I even tried changing value to formula, but results are the same and values are not auto-adjusted.

I am pasting my formula in text format as adviced.By 'desired result' I mean the numbers calculated by VBA are not the same as calculated by using formula. VBA calculations are faulty.

The image above was only indicative of a similar case.

After googling a bit, I am having thoughts if using an array with Redim preserve can solve the issue, in any way?

As solved by @Faneduru The issue was with using 'Activecell' in place of 'rng' and not with 'select'.

"I am not getting the desired result" - a bit vague. What is the desired result and what result are you getting? Also read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Jan 06 '20 at 11:21
Also don't add data as images, add them as plain text, so that other can copy your data and see if their solution works. — Luuklag, Jan 06 '20 at 11:28
You can apply a formula at once using `Range.Formula` if that would be an option for you. References in your formula would auto-adjust. Then you could use `Range.Value = Range.Value` if you want to get rid of formulas. — JvdV, Jan 06 '20 at 11:29
Does this answer your question? [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) You should get rid of the `ActiveCell` references — Luuklag, Jan 06 '20 at 11:29
Your iteration does not produce anything. Selection.Value will be all the time the same with ActiveCell. Can you explain IN WORDS what your code is necessary to do ? Just looking at it, I must confess I cannot understand and your explanations are a little foggy. — FaneDuru, Jan 06 '20 at 13:22
@FaneDuru Cell B3 should have value equaling =>average of A1 to A3.Cell B4 should calculate : (value of cell A4*(2/8-1)+ value of cell B3*(1-2/8+1)))) Cell B5 should calculate : (value of cell A5*(2/8-1)+ value of cell B4*(1-2/8+1)))) Cell B6 should calculate : (value of cell A6*(2/8-1)+ value of cell B5*(1-2/8+1)))) and so on….down the column — Biswajit, Jan 09 '20 at 09:54
@SJR By 'desired result' I mean the numbers obtained by VBA calculations are not the same as calculated by using formula. VBA calculations are faulty. — Biswajit, Jan 09 '20 at 09:57
@Luuklag hi! can you please advise me on how to paste data in plain text without losing formatting. All tables are getting merged. Also, the link suggested by you doesn't seem to solve the problem. — Biswajit, Jan 09 '20 at 09:59
@JvdV Sir, I had tried formula in place of value, but it didnt help the matter. — Biswajit, Jan 09 '20 at 10:03

FaneDuru · Accepted Answer · 2020-01-09T10:48:54.640

Try this code, please:

Sub ResultAchieved()
 Dim variableA As Long, sh As Worksheet, q As Long, rng As Range
 variableA = Worksheets("DATA").Range("D7").Value

 Set sh = Worksheets("MyCalculation")
 For q = 4 To 50 ' sh.Cells(sh.Rows.count, "A").End(xlUp).Row
    Set rng = sh.Range("B" & q)
    rng.value = (rng.Offset(0, -1).Value * (2 / (variableA + 1)) + _
               rng.Offset(-1, 0).Value * (1 - (2 / (variableA + 1))))
 Next q
End Sub

Using offset to copy formula down a column

1 Answers1