Auto fill formula with offset

Hi everyone,

I am currently making a module to automatically add columns in a sheet.
I would like to insert a new column and fill it on ~1000 lines with the formula =Bx/Ax if this column is C. The problem is that I don't know if the column is gonna be C or D or Z, so I guess I have to use offset to make something like =cell-1/cell-2.
A and B would be filled manually.

I'm new in VBA, I can't get it right to have the formula and then fill in all the column with it, can someone help me ?
Enclosed is a example of the result I'd like.

Thank you,

Jules

Try:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'
Dim lLR As Long, rStart As Range
' assume column requiring formulae is selected
Set rStart = ActiveCell

' check that we're in an acceptable column
If rStart.Column < 3 Then Exit Sub

Application.ScreenUpdating = False

' determine how many rows of data
lLR = Cells(Rows.Count, rStart.Column - 2).End(xlUp).Row

' put formula in cells and format as percentage
With Range(Cells(2, rStart.Column), Cells(lLR, rStart.Column))
    .FormulaR1C1 = _
    "=IF(AND(RC[-2]<>"""",RC[-1]<>""""),RC[-1]/RC[-2],"""")"
    .NumberFormat = "0%"
End With

Application.ScreenUpdating = True

End Sub

Has this resolved the issue?

I'm having an issue to auto hide some merged cells (a 64 wide cell, trying to identify a word inside and hide if it matches, but only hides the first of the 64 cols), if ever you anyone has an idea..

Different question; should be a new thread. However, try using Cell(...whatever...).Resize(,64).EntireColumn to refer to the range

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome. Thanks for the rep.