Copy forumla down

[Leanne M (Aussie)]

I have a user form for several columns of informaition and I have formulas in
others that I need to calculate with each new entry.
Could someone please advise how I can do this - my s/sheet ranges from A:AQ
and the formulas are in various different columns so there is no suitable
range.

Having read through the previous posts I have found the following post which
seems like it would solve my problem but does not-

Assume the formulas are in row 1

Sub ABC()
Dim rng as Range
Set rng = Cells(Rows.Count, "K").End(xlUp)
Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3)
End Sub

 

[Dave Peterson]

You'll still need to pick out a column that can be used to find that last row to
be filled. Tom used column A.

Sub ABC()
dim LastRow as long
with worksheets("Sheet1")
'change IV to the column that can be used to find that last row
lastrow = .cells(.rows.count,"IV").end(xlup).row

'just repeat this for each set of columns that has formulas
'it can be a single column or multiple columns.
.Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow)
.Range("f1").AutoFill .Range("f1").Resize(lastrow)
.Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow)
End Sub

 

[Leanne M (Aussie)]

Thanks Dave - As always a great help!

 

[Leanne M (Aussie)]

Hi Dave,

This is the code I have -
Sub ABC()
Dim LastRow As Long
With Worksheets("List")
'change IV to the column that can be used to find that last row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'just repeat this for each set of columns that has formulas
'it can be a single column or multiple columns.
.Range("A1").AutoFill .Range("A1").Resize(LastRow)
.Range("F1").AutoFill .Range("F1").Resize(LastRow)
.Range("L1").AutoFill .Range("L1").Resize(LastRow)
.Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow)
.Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow)
.Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow)
.Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow)
End Sub

I thought it had worked but it does not appear to. I have also tried a
different column to identify last row but still does not work.

I do not know if it makes a difference or not however I have the following
code at the start of my user form -
'find next empty row
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Please help a struggling code novice.

 

[Dave Peterson]

There was an "end with" missing.

But I'm not sure how you can use column A to determine how far the formula in A1
should be copied down.

I bet that there's plain old text in one of those columns -- so you can use that
column to determine the number of rows to fill down.

This suggestion:

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
is the equivalent of:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

And that means you would go down one row further that the row with the last used
value in that important column. (If you didn't start in row 1, then the
..resize() portion would be different.)





Sub ABC()
Dim LastRow As Long
With Worksheets("List")
'change IV to the column that can be used to find that last row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'just repeat this for each set of columns that has formulas
'it can be a single column or multiple columns.
.Range("A1").AutoFill .Range("A1").Resize(LastRow)
.Range("F1").AutoFill .Range("F1").Resize(LastRow)
.Range("L1").AutoFill .Range("L1").Resize(LastRow)
.Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow)
.Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow)
.Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow)
.Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow)
end with '<-- added
End Sub

 

[Leanne M (Aussie)]

Hi Dave,
Yes the End With is there just didnt copy into post.

I have tried both column D and column A. When column D didnt work (this
column is the first on that is filled in by the user form)

I think I am getting confused and conflicted with the fact that I need to
find the next blank row twice yet I need it to be the same blank row.

What I mean is that when the user form is initiated it needs to find the
next blank row to copy the information from the user form to the sheet. I
then need that recently completed row to have the formulas in other columns
populated.

I think this is where I am going wrong.

 

[Dave Peterson]

You find the next blank row to determine where to put the data from the
userform. So you can either use that in your code. Or you can find the last
used row (in column D, the text column) to use in the code.

They should be equivalent.

For instance:

dim NextRow as long
Dim LastRow as long

with worksheets("Sheet9999")
nextrow = .cells(.rows.count,"D").end(xlup).row + 1
'fill the cell with the value from the userform
.cells(nextrow, "D").value = "something non-empty"
end with

'then later...

with worksheets("Sheet9999")
lastrow = .cells(.rows.count,"D").end(xlup).row
'copy down formulas
end with

msgbox nextrow & vblf & lastrow

These two numbers will be the same--if you haven't done something else to the
data.

Hi Dave,
Yes the End With is there just didnt copy into post.

I have tried both column D and column A. When column D didnt work (this
column is the first on that is filled in by the user form)

I think I am getting confused and conflicted with the fact that I need to
find the next blank row twice yet I need it to be the same blank row.

What I mean is that when the user form is initiated it needs to find the
next blank row to copy the information from the user form to the sheet. I
then need that recently completed row to have the formulas in other columns
populated.

I think this is where I am going wrong.