Autofilling - Help With Column Loop

[LactoseO.D.'d]

I am trying to use autofill to populate the results of a formula on to a table.

I have no problems autofilling one column, which is where I'm at now:

(ignore the value errors, I will fix those later)

But I need to go across all the columns, similar to how I would if I just grabbed the box on the lower right of this selection across.

Of course the table size is undefined, so I have a suspicion I need to write some sort of loop that will repeat the column fill until I am out of columns with data.

Spreadsheet:
http://www.box.net/files#/files/0/f/0/1/f_871677463

Code so far:

Sub Macro2()
Dim RSQ As Range
' Macro2 Macro
'
'Create R2 Sheet
    Sheets.Add
    ActiveWindow.ActiveSheet.Name = "R2"
    Sheets("Dataset").Select
    Range("A1").Select
    ActiveCell.CurrentRegion.Select
    Selection.Copy
    Sheets("R2").Select
    ActiveSheet.Paste
    Range("C3").Select
    Range("A1").Value = "R2 Table"
    Rows("1:1").Hidden = True
    Columns("A:A").Hidden = True
 
'Define datset
    Sheets("Dataset").Select
    Range("C3").Select
    Sheets("R2").Select
    Range("C3").Select
    Sheets("Dataset").Select
    Range("C3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Names.Add Name:="DATA", RefersTo:=Selection
 
'Populate R2 Data
    Sheets("R2").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(LINEST(INDEX(data,0,RC2),INDEX(data,0,R2C),,TRUE),3,1)"
      Range("c3").AutoFill Destination:=Range("c3:c" & Range("d3").End(xlDown).Row)
End Sub

I've spent the day looking for sample code to do this -no dice.

 

[John_w]

If I understand correctly, you want to autofill from C3 across all columns to the right (column I in your example) and down all rows to the bottom (row 9 in your example). Try this:

    Dim lastRow As Long, lastColumn As Long
    
    lastRow = Cells(Rows.Count, "C").End(xlUp).row
    lastColumn = Cells(3, Columns.Count).End(xlToLeft).Column

    Range(Cells(3, "C"), Cells(3, lastColumn)).AutoFill Destination:=Range(Cells(3, "C"), Cells(lastRow, lastColumn)), Type:=xlFillDefault

 

[LactoseO.D.'d]

That ends up repeating itself. Instead of autofilling, it is repopulating the same formula over and over across the columns.

This is what I made last night. It autofills across an enormous range, then trims it back to the dataset. Its clunky, but it works.

'Populate R2 Data
    Sheets("R2").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(LINEST(INDEX(data,0,RC2),INDEX(data,0,R2C),,TRUE),3,1)"
    Range("C3:C2000").Select
    Selection.AutoFill Destination:=Range("C3:ZZ2000")
    Range("C2").Select
    Selection.End(xlToRight).Select
    Selection.Offset(1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents

 

[Norie]

Instead of autofilling why not returns simply setting the formula for all rgw cells in one go.

LR = Range("B" & Rows.Count).End(xlUp).Row

 Range("C3:D" & LR).FormulaR1C1 = YourFormula

 

[LactoseO.D.'d]

Thank you Norie. I recently expanded the format of the table and your code works while my old stuff ceased to function.

 

[Norie]

Glad it works.

Just wish the keyboard on my 'smart' phone worked a bit better - I can't even quite understand my post.

 

[LactoseO.D.'d]

I can't type on those things at all.

I have one final problem. I have my awesome code. It all works. Now I'm adding it to an add-in.

The add-in creates a sheet from a hidden sheet as a menu for a couple of functions. I want to add my macro to them.

So I created a button on the hidden sheet template, and tied it to my macro by selecting assign macro-> Macro_Name. I have the macro declared as global in a module.

So now, I go to the add-in menu, select the menu, and it populates the sheet with my button on it. Unfortunately when I go to click the macro button while running the add-in, I find it is tied to something like Book3!Macro_Name.

Reassigning the button to Macro_Name, makes it work just fine. Now how do I get it to come up that way?

 

[LactoseO.D.'d]

Any ideas? I feel like this last part should be really easy, but I'm missing something. I don't know why the macro name comes up wrong when I assign it on the hidden worksheet.

 

[LactoseO.D.'d]

Ok I have made some progress.

The error was caused by assigning a macro to a normal button on the hidden worksheet. When the hidden worksheet was copied over, the references did not carry over. I have instead used a command button, and linking that to my macro, which seems to work.

Private Sub CommandButton3_Click()
    Call MacroName
End Sub

However, I have an issue as to where I store the Macro. Though the macro now executes, it only does so if the code for it is stored on the worksheet object. This gives a 1004 error because the macro references another worksheet. When I put the macro in a module and tried making the macro global, the reference to it on the command button no longer works. How do I fix this?