automating a repetitive process

vthomeschoolmom · #1 02-28-2012, 01:23 PM

I am a recovering VB developer, so I know VBA. I am not super familiar with the Excel object model. I want to write a macro in a workbook. I have an unknown # of rows of data. I want to navigate to the first row that does not contain data. I then want to create a function in that cell =MAX(A2, Ax) where x is the last row of data. I then want to replicate that formula for each column until the first column without data is reached.

Does anyone have pointer to a referenced? Do I somehow iterate through the rows until I get an empty one? And likewise the columns?

A nudge would be useful. Thanks

S

JBeaucaire · #2 02-28-2012, 07:41 PM

No need to iterate or loop. You can spot the LastRow (LR) and LastColumn (LC), store them in a variable, the insert the formulas all at once using the R1C1 syntax:

Code:

Option Explicit

Sub AddFormulas()
Dim LR As Long, LC As Long

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Range(Cells(LR + 1, 1), Cells(LR + 1, LC))
        .FormulaR1C1 = "=MAX(R2C:R[-1]C)"
    End With

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PowerPoint 2007 process hangs	ChrisK	PowerPoint	6	02-03-2012 02:00 PM
Automating daily process	dreww2	Outlook	0	06-28-2011 07:25 PM
repetitive work in word	vishalvijayan	Word	1	03-31-2011 08:30 PM
Configuration process problem	Dunn2009	Outlook	0	10-02-2009 02:38 AM
Auto process meeting acceptances	arrosen	Outlook	0	06-14-2006 12:49 PM