Excel VBA code for simple formula between cells

0
votes

enter image description here

I need a code for these orders:

  1. Go to first blanks cell in column C.
  2. Calculat the number in column D minus 14 and do it as long there is a number right to the cell in D (there is blanks in column D)
  3. Copy the result in column C and paste it as values to Column B. note: I need it to stay in original rows.

` OP_wb.Sheets("Optic Main").Activate Dim FirstRow As Range Dim LastRow As Range Set FirstRow = Range("C1").End(xlDown).Offset(1, 0)

Range("E1").End(xlDown).Offset(0, -2).Select
Range(FirstRow, LastRow).FormulaR1C1 = "=(c4-14)"`
1
excelvbaexcel-formularange
@TimWilkinson that is what I have: OP_wb.Sheets("Optic Main").Activate Range("C1").End(xlDown).Offset(1, 0).Activate ActiveCell.FormulaR1C1 = "=RC[1]-14" – Rafael Osipov
here is where i stuck now: Dim FirstRow As Range Dim LastRow As Range Set FirstRow = Range("C1").End(xlDown).Offset(1, 0) Set LastRow = Range("E1").End(xlDown).Offset(0, -2) Range(FirstRow, LastRow).FormulaR1C1 = "=(RC4-14)" – Rafael Osipov

1 Answers

0
votes

try this:

1.

Set FirstRow = Range("C1").End(xlDown).Offset(1, 0)
Set lastrow = Range("E1").End(xlDown).Offset(0, -2)

Range("E1").End(xlDown).Offset(0, -2).Select

2.

Range(FirstRow, lastrow).FormulaR1C1 = "=(c4-14)"

3.

Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lastrow)

hope this helps