Excel VBA to fill a column with formula until (last filled cell in another column)

microsoft excelvbaworksheet-function

So I have an Excel Sheet, Column A is filled with data (the range is different everytime)
I want to fill the empty col H with formula (from H2 to the last filled cell in colA)

This is the code where I referred from multiple sites.
But it only filled H1 and H2, where I want H2:H6 in this case.
Current output using xlUp.
colC,D,E,F are something that can be ignored at the moment.

This is the current output using xlUp

I tried to change xlUp to xlDown.
It worked but it filled every cell from H2 to H(infinity), when some of data in colA stop at row60.

Sub Try()

Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Sheet4").Select
Range("H2:H" & lRow).Select
Worksheets("Sheet4").Range("H2").FormulaR1C1 = "=(RC[-6])/(RC[-1])"

Range("H2").AutoFill Destination:=Range("H2:H" & lRow)

End Sub

Can anyone please point out my mistake?

I know some of the method using Active Cells, but I don't want to use it because sometimes I don't know where my active cell is? like I dont know which cell is currently selected. (please let me know if I got the wrong idea of active cell)

Thank you.

Best Answer

This should work for you. It selects range of data in column A and then offsets it to the column H. This range will be given to the auto-fill.

Option Explicit

Dim rng As Range
Dim wsh As Worksheet

Sub Second_Try()

  Set wsh = Worksheets("Sheet4")
  wsh.Activate

  wsh.Cells(1, "H") = "ans."

  Set rng = Range(wsh.Cells(2, 1), wsh.Cells(2, 1).End(xlDown))

  Set rng = rng.Offset(0, 7)

  wsh.Range("H2").FormulaR1C1 = "=(RC[-6])/(RC[-1])"
  Range("H2").AutoFill Destination:=rng

  'instead of these last two rows you can simply use this:
  'rng.FormularR1C1 = "=(RC[-6])/(RC[-1])"

End Sub

I personally prefer this solution since it does not selects ranges and just deals with the cells till there's an empty cell.

Option Explicit

Dim i As Integer
Dim wsh As Worksheet

Sub Third_Try()

 Set wsh = Worksheets("Sheet4")

 i = 2
 While wsh.Cells(i, 1) <> ""

  wsh.Cells(i, 8).FormulaR1C1 = "=(RC[-6])/(RC[-1])"

  i = i + 1
 Wend

End Sub
Related Question