Thread: Selecting Range with Variable Number of Columns

I am trying to apply a formula on one worksheet that references cells on a previous worksheet. all cells have the same formula (relative references) but the number of columns may change depending on the data inputted in the "Data" tab.

i would like to know if there is a cleaner way to have a formula applied to a range with variable number of columns.

currently i am using two loops and applying the formula to each cell until Column A is empty and Row 1 is empty:

...

Dim c As Integer, d As Integer

c = 1
d = 0

Application.ScreenUpdating = False

Sheets("Log Return").Select
Range("B7").Select

Do Until IsEmpty(ActiveCell.Offset(-6, 0))
    Do Until IsEmpty(ActiveCell.Offset(0, -c))
    ActiveCell.FormulaR1C1 = "=LOG('Data'!RC/'Data'!R[-5]C)"
    ActiveCell.Offset(1, 0).Select
    d = d + 1
    Loop
    ActiveCell.Offset(-d, 1).Select
    d = 0
    c = c + 1
    Loop
c=1
Application.ScreenUpdating = True

...

what i want is to apply this formula:

FormulaR1C1 = "=LOG('Data'!RC/'Data'!R[-5]C)"

to a range that may have 4 columns or may have 104 columns (or any number)

my current code takes a while to run because it runs through each cell independently. any help to make this smoother is much appreciated!

You can check for the last column using =

 lcol=range("XFD1").End(xltoLeft).Column

Is it possible for your data to have named ranges? The formula could then reference a range name. This could be either worksheet-specific or at the workbook level.
A named range of variable size can be created using the following as an example

Sub test()
Dim rWR1 As Range
Dim lColumns As Long

lColumns = 30
Set rWR1 = Worksheets("Sheet1").Range("A2").Resize(1, lColumns)

Worksheets("Sheet1").Names.Add Name:="MyTest", RefersTo:=rWR1

End Sub

If refering to a sheet-level name in a folmula on a different sheet, the sheet name needs to be added.
Having a relevant name in a formula makes the logic easier to follow.

Also, using copy & paste in code works well - set up the first column of formulae, establish the target range, copy first column then paste to target.
Hope this helps.

Perhaps

Dim lRow as long, lCol as long
With Sheets("Log Return")
lRow = .cells(.rows.Count, "A").end(xlup).row
lCol = .cells(1, .columns.count).End(xltoleft).column
.range("B7", .cells(lrow, lcol)).formular1c1 = "=LOG('Data'!RC/'Data'!R[-5]C)"
End With

Glad to assist.

Please see the FAQ link at the top of the page for instructions on marking the thread Solved. (you need to change the first post, not the last)