Autofill Question

[Guest]

I would like to use the autofill function but am not sure the best way to
start. I have a spreadsheet that will vary on the number of columns of data
and rows. I want to find the first blank column, select the second row
(first row I will place a header) place a formula in the second row and
autofill to the last row of data.

Assume current sheet has columns A:E
Assume 100 rows of data
Assume formula to autofill in column is A2*C2

Hopefully this makes sense,

Thanks,

SJ

 

[Guest]

Dim lastrow as Long, lastcol as Long
lastrow = cells(rows.count,1).End(xlup).Row
lastcol = cells(1,columns.count).End(xlToLeft).Column

cells(2, lastcol + 1).Resize(lastrow-1, 1).Formula = "=A2*C2"

 

[Dave Peterson]

Since you're putting headers in row 1, can you use row 1 to determine the next
available column?

And can you pick out a column that can be used to determine the last used row.

I used row 1 and column A in the following:

dim LastRow as long
dim NextCol as long
dim wks as worksheet

set wks = worksheets("sheet999")

with wks
NextCol = .cells(1,.columns.count).end(xltoleft).column + 1
lastrow = .cells(.rows.count,"A").end(xlup).row

.range(.cells(2,nextcol),.cells(lastrow,nextcol)).formula _
= "=a2*c2"
end with

(untested, uncompiled. Watch for typos.)

 

[Guest]

Tom,

Thanks for getting back to me. This works perfectly.

Thanks for the help, I really appreciate it.

Scott J

 

[Guest]

Dave,

Thanks for getting back to me. Your code works perfectly just the way you
have it. Thanks for the help, I really do appreciate it. Both you and Tom
are awesome at coding. I am a beginner and am continually learning. It is
amazing the number of ways you can get the desired result with a slightly
different approach.

I have browsed through alot of the responses on the boards and I think you
guys are the most consistent and best out there.

Thanks again,

Scott J