Ramil,
Yuri gave you the command you need. You don't need to have it as a macro. Just use in your automation code tweaking bit :

oExcel.ActiveWorkbook.ActiveSheet.Range('B1:B20').FormulaR1C1 = "=RC[-1]+1"

Sets all cell values in B1:B20 to 1 + corresponding A1:A20 value.

With Offset() you could have 1st parameter as 0 to specify currentRow.

ie:

oExcel = Createobject('Excel.application')
With oExcel
  .Workbooks.Add
  .Visible = .T.
  With .ActiveWorkbook.ActiveSheet
    For ix=1 To 20
      .Cells(ix,1).Value = ix
    Endfor

* FormulaR1C1 - B1 = A1 * 3
    .Range('B1:B20').FormulaR1C1 = '=RC[-1]*3'

* Offset worksheet function - E1 = B1 + A1
    .Range('E1:E20').FormulaR1C1 = ;
       '=Offset(R[0]C[0],0,-3)+Offset(R[0]C[0],0,-4)'


    For ix=25 To 45
      .Cells(ix,1).Value = ix
      .Cells(ix,2).Activate
* ActiveCell and Offset
      .Application.ActiveCell.Value = ;
        .Application.ActiveCell.Offset(0,-1).Value*2
    Endfor
  Endwith
Endwith

FormulaR1C1 is better as it's a single command plus it really writes an updatable formula rather than a direct value.
Cetin

>This will work inside a macro. Again, I need to use a worksheet function because we don't want to send excel files containing macros.
>
>>Maybe this? It takes value from the cell on the very same row, previous column,and add 1:
>>
>>ActiveCell.FormulaR1C1 = "=RC[-1]+1"
>>
>>>I need to grab the data from a cell based on the current row but I can't use a macro. It has to be a worksheet formula.
>>>
>>>>Is it what you need?
>>>>
>>>>ActiveCell.Offset(rowOffset, columnOffset).Activate
>>>>
>>>>>Hi all,
>>>>>
>>>>>I posted a similar question to an Office Dev forum and got no reply. I guess nobody was home.
>>>>>
>>>>>Anyway, this is an excel question but I was hoping someone knows how to do this. How do I automatically update a value based on the current cell without using macro.
>>>>>

>>>>>Example:
>>>>>  A1 (R1C1) = 100 && active cell
>>>>>  B1 (R2C1) = 200
>>>>>
>>>>>Formula in E1
>>>>> = offset(currentRow,0,0,1) && how do I get currentRow to be equal to the activecell row.
>>>>>