Search code examples
excelvbaoffset

Using offset to copy formula down a column

ScreenShot

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'.

Solution

  • 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